Excel видаляє символи, слова, цифри з текстових рядків

Припустимо, у вас є довгий список текстових рядків, які містять символи, цифри або інші специфічні символи. У певних випадках вам може знадобитися видалити деякі символи на основі позиції, наприклад, справа, зліва чи посередині з текстових рядків, або видалити деякі небажані символи, числа зі списку рядків. Знаходження рішень по одному призведе до головного болю. У цьому посібнику зібрано всі види методів видалення символів, слів або чисел у Excel.

---

Видаліть символи зліва, справа або посередині текстових рядків

Для більшості з нас може бути звичайною роботою видалити деякі символи зліва, справа або посередині текстових рядків на робочих аркушах Excel. У цьому розділі буде представлено кілька швидких і простих прийомів для вирішення цього завдання.

1.1 Видаліть перші n символів із текстових рядків

Якщо вам потрібно видалити перші n символів зі списку текстових рядків, наведені нижче методи можуть допомогти вам.

 За допомогою формул

Зазвичай, щоб видалити символи з початку текстових рядків, можна використовувати функцію REPLACE або комбінацію функцій RIGHT і LEN.

Функція REPLACE для видалення перших N символів:

• рядок: текстовий рядок, з якого потрібно видалити символи;
• кількість_символів: кількість символів, які потрібно видалити.

Наприклад, щоб видалити перші 2 символи з комірок, скористайтеся формулою нижче, а потім перетягніть маркер заповнення, щоб скопіювати формулу в інші клітинки, дивіться знімок екрана:

Функції RIGHT і LEN для видалення перших N символів:

• рядок: текстовий рядок, з якого потрібно видалити символи;
• кількість_символів: кількість символів, які потрібно видалити.

Щоб видалити перші 2 символи з клітинок, застосуйте таку формулу:

---

 За допомогою функції, визначеної користувачем

Щоб видалити перші n символів із комірок, ви також можете створити визначену користувачем функцію для вирішення цього завдання. Будь ласка, виконайте такі дії:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у вікно модуля.

Код VBA: видалити перші n символів із текстових рядків

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Потім поверніться до аркуша та введіть цю формулу: = removefirstx (A4,2) в порожню комірку, а потім перетягніть маркер заповнення вниз, щоб отримати результати, як вам потрібно, див. знімок екрана:

примітки: У цій формулі: A4 це клітинка, з якої потрібно видалити символи; Кількість 2 вказує кількість символів, які ви хочете видалити з початку текстового рядка.

---

1.2 Видалити останні n символів із текстових рядків

Щоб видалити певну кількість символів з правого боку текстових рядків, ви також можете використовувати формулу або функцію, визначену користувачем.

 За допомогою формули

Щоб видалити останні n символів із текстових рядків, можна використовувати формулу, засновану на функціях LEFT та LEN.

Функції LEFT і LEN для видалення останніх N символів:

• рядок: текстовий рядок, з якого потрібно видалити символи;
• кількість_символів: кількість символів, які потрібно видалити.

Щоб видалити 3 символи з кінця текстових рядків, скористайтеся цією формулою, а потім перетягніть маркер заповнення, щоб скопіювати формулу в інші клітинки, дивіться знімок екрана:

---

 За допомогою функції, визначеної користувачем

Тут Визначена користувачем функція також може допомогти вам видалити останні n символів зі списку комірок, будь ласка, зробіть так:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у вікно модуля.

Код VBA: видалити останні n символів із текстових рядків

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Потім поверніться до робочого аркуша та введіть цю формулу: = removelastx (A4,3) в порожню комірку, а потім перетягніть маркер заповнення вниз, щоб отримати результати, як вам потрібно, див. знімок екрана:

примітки: У цій формулі: A4 це клітинка, з якої потрібно видалити символи; Кількість 3 вказує кількість символів, які ви хочете видалити з кінця текстового рядка.

---

1.3 Видаліть перші, останні n символів або символи певної позиції за допомогою потужної функції

Вам може бути боляче запам’ятовувати різні формули, видаляти символи зліва, справа чи певної позиції текстових рядків, Kutools для Excel підтримує потужну функцію - Видалити за позицією. За допомогою цього невеликого інструменту ви можете впоратися з цими завданнями кількома клацаннями, не запам’ятовуючи жодних формул.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть клітинки, символи яких потрібно видалити, а потім клацніть Кутулс > текст > Видалити за позицією, див. скріншот:

2, в Видалити за позицією діалоговому вікні, виконайте такі дії:

2.1 Видалити перші n символів із клітинок:

• In Номери текстове поле, введіть кількість символів, які потрібно видалити з рядків. У цьому прикладі я видалю перші 2 символи.
• Select Зліва варіант в становище .
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб отримати результат, як показано нижче.

2.2 Видалити останні n символів із клітинок:

• In Номери текстове поле, введіть кількість символів, які потрібно видалити з рядків. У цьому прикладі я видалю останні 3 символи.
• Select Справа варіант в становище .
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб отримати результат, як показано нижче.

2.3 Видалити n символів із певної позиції клітинок:

Наприклад, якщо вам потрібно видалити певну кількість символів з певної позиції текстових рядків, видалення 3 символів починається з третього символу рядків.

• In Номери текстове поле, введіть кількість символів, які потрібно видалити з рядків. У цьому прикладі я видалю 3 символи з певної позиції.
• Select Вказувати і введіть число, з якого потрібно видалити символи, починаються в текстовому полі становище розділ. Тут я видалю символи з третього символу.
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб отримати результат, як показано нижче.

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

1.4 Видаліть як перші n, так і останні n символів із текстових рядків за допомогою формули

Якщо вам потрібно видалити деякі символи з обох сторін текстових рядків у Excel, ви можете об’єднати функції MID і LEN, щоб створити формулу для вирішення цього завдання.

• рядок: текстовий рядок, з якого потрібно видалити символи;
• лівий_символ: кількість символів для видалення зліва;
• правий_символ: кількість символів, які потрібно видалити справа.

Наприклад, вам потрібно видалити перші 7 символів і останні 5 символів із текстових рядків одночасно, будь ласка, введіть таку формулу в порожню клітинку:

примітки: У цій формулі: A4 це клітинка, з якої потрібно видалити символи; Кількість 7 – кількість символів, які потрібно видалити з лівого боку; Кількість 5 – кількість символів, які потрібно видалити з правого боку.

А потім перетягніть маркер заповнення вниз туди, де ви хочете застосувати цю формулу, і ви отримаєте результат, як показано нижче:

---

Видаліть небажані/спеціальні символи з текстових рядків

Під час імпортування даних з іншого місця до Excel на ваш аркуш може бути вставлено багато спеціальних або небажаних символів. Щоб видалити ці небажані символи, такі як #@$%^&, пробіли, числа, нечислові числа, розриви рядків тощо, у цьому розділі будуть наведені корисні методи, які допоможуть вам.

2.1 Видаліть деякі спеціальні символи з текстових рядків

Якщо в текстових рядках є спеціальні символи, наприклад %^&*(), щоб видалити цей тип символів, ви можете застосувати три наведені нижче прийоми.

 Видаліть кілька спеціальних символів із текстових рядків за допомогою функції SUBSTITUTE

Зазвичай в Excel можна вкладати кілька функцій SUBSTITUTE, щоб замінити кожен конкретний символ нічим, загальний синтаксис такий:

• комірка_рядка: клітинка містить текстовий рядок, з якого потрібно видалити спеціальні символи;
• символ1, символ2, символ3: небажані символи, які потрібно видалити.

Тепер скопіюйте або введіть формулу нижче в порожню клітинку:

А потім перетягніть маркер заповнення вниз до клітинок, де ви хочете застосувати цю формулу, і всі вказані вами небажані символи будуть видалені відразу, дивіться знімок екрана:

Tips : Якщо є більше символів, які ви хочете видалити, вам просто потрібно вкласти більше функцій SUBSTITUTE у формулу.

---

 Видаліть кілька спеціальних символів із текстових рядків за допомогою функції, що визначає користувач

Наведені вище вкладені функції SUBSTITUTE добре працюють, якщо потрібно видалити кілька спеціальних символів, але якщо у вас є десятки символів, які потрібно видалити, формула стане занадто довгою та важкою для керування. У цьому випадку наведена нижче функція, визначена користувачем, може допомогти вам швидко та легко виконати це завдання.

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалити кілька спеціальних символів із текстових рядків

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Потім закрийте вікно коду та поверніться до робочого аркуша, введіть цю формулу =Видалити небажані символи(A2, $D$2) у порожню клітинку, де вивести результат, а потім перетягніть маркер заповнення вниз, щоб отримати потрібні результати, дивіться знімок екрана:

примітки: У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити символи; $ D $ 2 містить спеціальні символи, які ви хочете видалити (ви можете ввести будь-які інші спеціальні символи, які вам потрібні).

---

 Видаліть кілька спеціальних символів із текстових рядків за допомогою дивовижної функції

Якщо ви встановили Kutools для Excel, З його Видалити символи Ви можете видалити зі списку комірок усі види символів, як-от числові символи, букви, недруковані символи…

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон комірок, з яких потрібно видалити спеціальні символи, а потім клацніть Кутулс > текст > Видалити символи, див. скріншот:

2, в Видалити символи діалогове вікно:

• перевірити виготовлений на замовлення опція під Видалити символи .
• А потім введіть спеціальні символи в текстове поле, яке потрібно видалити.
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб відразу видалити вказані вами символи. Дивіться знімок екрана:

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

2.2 Видалити всі числа з текстових рядків

Якщо у вас є список текстових рядків, які змішуються з цифрами, літерами та спеціальними символами, і тепер ви хочете просто видалити всі цифри та зберегти інші символи. У цьому розділі наведено кілька зручних способів, які допоможуть вам.

 Видаліть числа з текстових рядків за допомогою функції SUBSTITUTE

У Excel вкладена функція SUBSTITUTE може допомогти замінити всі числа нічим, тому ви можете використовувати наведену нижче формулу, щоб видалити всі числа з клітинок:

Потім перетягніть маркер заповнення до клітинок, де ви хочете застосувати цю формулу, і всі числа будуть видалені зі списку текстових рядків, дивіться знімок екрана:

---

 Видаліть числа з текстових рядків за допомогою функції TEXTJOIN

Якщо у вас є Excel 2019, 2021 або 365, нова функція TEXTJOIN також може допомогти видалити числа з текстових рядків.

Будь ласка, скопіюйте таку формулу в порожню клітинку, а потім натисніть клавіші Ctrl + Shift + Enter разом, щоб отримати перший результат:

А потім скопіюйте формулу в інші клітинки нижче, де ви хочете застосувати цю формулу, дивіться знімок екрана:

примітки: Цей TEXTJOIN доступний лише в Excel 2019, 2021 та Office 365.

---

 Видалення чисел із текстових рядків за допомогою функції, визначеної користувачем

Окрім двох вищевказаних формул, функція, визначена користувачем, також може зробити вам послугу, виконайте такі дії:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалити числа з текстових рядків

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Потім закрийте та вийдіть із вікна коду, поверніться до робочого аркуша та введіть цю формулу: = RemoveNumbers (A2) у порожню клітинку, а потім перетягніть маркер заповнення вниз до клітинок, де ви хочете застосувати цю формулу, дивіться знімок екрана:

---

 Видаліть числа з текстових рядків за допомогою зручної опції

Якщо ви втомилися від складних формул, дозвольте мені показати вам простий інструмент – Kutools для ExcelАвтора Видалити символи. За допомогою цієї зручної функції ви можете виконати це завдання лише кількома кліками.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон комірок, де потрібно видалити числа, а потім клацніть Кутулс > текст > Видалити символи.

2, в Видалити символи діалоговому вікні, виконайте такі дії:

• перевірити Числовий опція під Видалити символи .
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб негайно видалити цифри. Дивіться знімок екрана:

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

2.3 Видалити нечислові символи з текстових рядків

Щоб видалити всі нечислові символи та зберегти лише числа з текстових рядків, у цьому розділі буде розказано про деякі способи вирішення цього завдання в Excel.

 Видаліть нечислові символи з текстових рядків за допомогою формули в Excel 2016 та попередніх версіях

Якщо ви використовуєте Excel 2016 або попередні версії, вам слід застосувати складну формулу для виконання цієї роботи, будь ласка, скопіюйте або введіть наведену нижче формулу в порожню клітинку:

А потім скопіюйте формулу в інші клітинки нижче, де ви хочете застосувати цю формулу, дивіться знімок екрана:

примітки: Якщо числа в текстовому рядку починаються з 0, 0 буде втрачено.

---

 Видаліть нечислові символи з текстових рядків за допомогою функції TEXTJOIN в Excel 2019, 2021, 365

Наведена вище формула може бути занадто важкою для розуміння більшості з нас. Якщо у вас є Excel 2019, 2021 або 365, є чітка формула, яка може вам допомогти.

Скопіюйте або введіть таку формулу у порожню клітинку та натисніть Ctrl + Shift + Enter ключі разом, щоб отримати перший правильний результат:

Потім перетягніть маркер заповнення до клітинок, де ви будете застосовувати цю формулу, і ви отримаєте результат, як показано нижче:

примітки: За допомогою цієї формули ви можете побачити, що початкові нулі будуть збережені, оскільки числа повертаються як текст.

---

 Видаліть нечислові символи з текстових рядків за допомогою функції, що визначає користувач

Звичайно, ви також можете створити власну визначену користувачем функцію з простішим синтаксисом, будь ласка, зробіть це так:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видаліть нечислові символи з текстових рядків

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Потім закрийте та вийдіть із вікна коду, поверніться до робочого аркуша та введіть цю формулу: =Видалитичисловий(A2) у порожню клітинку, а потім перетягніть маркер заповнення вниз до клітинок, де ви хочете застосувати цю формулу, будуть витягнуті лише числа, як показано нижче:

---

 Видаліть нечислові символи з текстових рядків за допомогою простої функції

Щоб безпосередньо видалити нечислові символи в діапазоні комірок, Kutools для ExcelАвтора Видалити символи утиліта може зробити це лише кількома кліками.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон комірок, з яких потрібно видалити нечислові символи, а потім клацніть Кутулс > текст > Видалити символи.

2, в Видалити символи у діалоговому вікні встановіть такі операції:

• перевірити Нечислові опція під Видалити символи .
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб негайно видалити всі нецифрові символи. Дивіться знімок екрана:

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

2.4 Розділіть текст і числа з однієї клітинки на дві колонки

Іноді вам може знадобитися витягнути текст і числа з текстових рядків у два відокремлені стовпці, за допомогою наведених нижче методів ви можете швидко та легко виконати це завдання.

 Розділіть текст і числа з однієї клітинки на два стовпці за допомогою функції, що визначає користувач

Використовуючи наведену користувачем функцію, ви можете витягти текст і числа одночасно, виконайте наведені нижче дії.

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: розділіть текст і числа з текстових рядків на два стовпці

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Потім закрийте та вийдіть із вікна коду, поверніться до робочого аркуша та введіть цю формулу: = SplitText (A2, FALSE) у порожню клітинку, а потім перетягніть маркер заповнення вниз до клітинок, які ви хочете заповнити цією формулою, щоб отримати весь текст, див. знімок екрана:

4. А потім продовжуйте вводити цю формулу: = SplitText (A2, TRUE) в іншу комірку і перетягніть маркер заповнення вниз до комірок, які ви хочете заповнити цією формулою, щоб отримати цифри, див. знімок екрана:

---

 Розділіть текст і числа з однієї клітинки на дві колонки за допомогою легкої функції

Якщо у вас є Kutools для Excel, його Розділені клітини Утиліта може допомогти вам розділити клітинки на кілька стовпців або рядків на основі будь-яких роздільників, заданої ширини або тексту та числа.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон комірок, який потрібно розділити, а потім клацніть Кутулс > Злиття та розділення > Розділені клітини, див. скріншот:

2, в Розділені клітини діалогове вікно, виберіть Розділити на стовпці опція під тип розділ, а потім перевірте Текст і номер від Розділено на розділ, див. знімок екрана:

3. Потім натисніть кнопку Ok кнопку та іншу Розділені клітини з’явиться діалогове вікно, виберіть комірку для виведення розділеного тексту та чисел, а потім натисніть кнопку OK кнопку. Тепер ви можете побачити, як текст і числа у вибраних клітинках розділені на два стовпці одночасно, як показано нижче:

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

2.5 Видалити символи розриву рядка з текстових рядків

Розрив рядка – це те, що дозволяє мати кілька рядків в одній клітинці в Excel. Іноді, коли ви копіюєте дані з веб-сайту або відокремлюєте вміст клітинки за допомогою Alt + Enter клавіші вручну, ви отримаєте розриви рядків або повернення каретки. У певному випадку ви можете видалити розриви рядків, щоб вміст клітинки перетворився на один рядок, як показано нижче. Тут я розповім кілька способів вирішення цього завдання в Excel.

 Видаліть символи розриву рядка з текстових рядків за допомогою функції «Знайти та замінити».

В Excel ви можете використовувати Знайти і замінити функція видалення розривів рядків, будь ласка, зробіть так:

1. Виберіть діапазон даних, з якого потрібно видалити розриви рядків.

2. Потім натисніть кнопку Головна > Знайти та вибрати > заміщати (або натисніть Ctrl + H, ключі), щоб перейти Знайти і замінити діалогове вікно, див. знімок екрана:

3. В вискочив Знайти і замінити діалоговому вікні, виконайте такі дії:

• Помістіть курсор у Знайти те, що поле і натисніть Ctrl + J на клавіатурі ви можете нічого не побачити, але символ розриву рядка вставлено.
• У Замініть поле, залиште це поле порожнім, щоб просто видалити розриви рядків, або натисніть кнопку Космічний баr один раз, щоб замінити розриви рядків пробілами.

4. Потім натисніть кнопку замінити всі кнопки, усі розриви рядків у виділених клітинках будуть видалені або замінені пробілами відразу. Дивіться знімок екрана:

---

 Видаліть символи розриву рядка з текстових рядків за допомогою функції SUBSTITUTE

Ви також можете створити формулу на основі функцій SUBSTITUTE і CHAR, щоб видалити розриви рядків із текстових рядків.

Будь ласка, застосовуйте формулу нижче, щоб отримати результат:

Tips : Функція SUBSTITUTE знаходить та замінює символ CHAR(10), який представляє символ розриву рядка без нічого. Якщо ви хочете, щоб результат був розділений комою та пробілом, ви можете використовувати наступну формулу:

---

 Видаліть символи розриву рядка з текстових рядків за допомогою коду VBA

Якщо вам зручно використовувати код VBA, тут також надається код для вас, виконайте такі дії:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видаліть розриви рядків із текстових рядків

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Потім натисніть F5 клавішу, щоб запустити цей код, і з'явиться вікно з підказкою, виберіть діапазон, який ви хочете видалити розриви рядків, дивіться знімок екрана:

4. А потім натисніть OK кнопку, усі розриви рядків будуть видалені з вибраного діапазону даних.

---

 Видаліть символи розриву рядка з текстових рядків за допомогою розумної опції

Тут, Kutools для ExcelАвтора Видалити символи Ця функція також може допомогти вам легко видалити розриви рядків.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон комірок, де потрібно видалити розриви рядків, а потім клацніть Кутулс > текст > Видалити символи.

2, в Видалити символи у діалоговому вікні встановіть такі операції:

• перевірити Недруковані опція під Видалити символи .
• Потім натисніть кнопку Ok or Застосовувати кнопку, щоб видалити всі розриви рядків із вибраного діапазону даних. Дивіться знімок екрана:

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

2.6 Видалити пробіли (початкові, кінцеві, зайві або всі пробіли) з текстових рядків

Створення та вставлення тексту із зовнішнього джерела на робочий аркуш Excel часто викликає неприємні пробіли, тому видаляти початкові, кінцеві чи інші додаткові пробіли вручну буде нудно. На щастя, Excel пропонує кілька простих прийомів для вирішення цього завдання.

 Видаліть зайві пробіли (на початку, в кінці, надлишок) із текстових рядків за допомогою функції TRIM

У Excel, щоб видалити початкові, кінцеві та додаткові пробіли з текстових рядків, вам може допомогти проста функція TRIM. Ця функція видаляє всі пробіли, крім одиничних пробілів між словами.

Введіть формулу нижче в порожню комірку:

А потім перетягніть маркер заповнення вниз, щоб скопіювати формулу для інших комірок, тепер ви можете побачити, що всі початкові, кінцеві пробіли та додаткові пробіли серед слів видаляються відразу зі знімка екрана:

---

 Видаліть усі пробіли з текстових рядків

Якщо ви хочете видалити всі пробіли з текстових рядків, наведена нижче функція ЗАМІНИ та функція «Знайти та замінити» можуть зробити вам послугу.

За допомогою функції SUBSTITUTE

Ви можете використовувати функцію SUBSTITUTE, щоб замінити всі пробіли нічим, будь ласка, застосуйте наведену нижче формулу до порожньої клітинки:

Потім перетягніть маркер заповнення вниз, щоб скопіювати цю формулу в інші потрібні клітинки, і всі пробіли будуть видалені, як показано нижче:

---

За допомогою функції «Знайти та замінити».

У самому справі, Знайти та замінити функція в Excel також може допомогти позбутися всіх пробілів у вибраних клітинках, виконайте такі дії:

1. Виберіть діапазон даних, з якого потрібно видалити всі пробіли.

2. Потім натисніть кнопку Головна > Знайти та вибрати > заміщати (або натисніть Ctrl + H, ключі), щоб перейти до Знайти і замінити діалоговому вікні, що відкриється Знайти і замінити діалоговому вікні, виконайте наведені нижче дії:

• прес Пробіл в Знайти те, що поле;
• У Замініть поле, залиште це поле порожнім.

3. А потім натисніть замінити всі кнопку, усі пробіли у виділених клітинках будуть видалені одночасно. Дивіться знімок екрана:

---

 Видаліть усі види пробілів із текстових рядків за допомогою потужної функції

Kutools для Excel має потужну функцію - Видалити пробіли, за допомогою цієї утиліти в одному діалоговому вікні ви можете видалити не тільки початкові, кінцеві, зайві пробіли, а й усі пробіли з вибраних діапазонів, що підвищить продуктивність вашої роботи.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон даних, з якого ви видалите пробіли, а потім клацніть Кутулс > текст > Видалити пробіли. Дивіться знімок екрана:

2, в Видалити пробіли діалоговому вікні виберіть один тип простору, який потрібно видалити з Тип простору:

• Видаліть перші пробіли, будь ласка, виберіть Провідні простори варіант;
• Видаліть пробіли в кінці, будь ласка, виберіть Кінцеві пробіли варіант;
• Відразу видаліть початкові та кінцеві пробіли, будь ласка, виберіть Провідні та кінцеві пробіли варіант;
• Видаліть усі зайві пробіли, будь ласка, виберіть Усі зайві місця варіант;
• Видаліть усі пробіли, будь ласка, виберіть Усі пробіли варіант.

3. Потім натисніть кнопку Ok or Застосовувати кнопку, ви отримаєте потрібний результат.

Завантажте та безкоштовно пробуйте Kutools для Excel зараз!

---

Видаліть символи/текст до або після певного символу

У цьому розділі я розповім про деякі операції для видалення тексту або символів до або після першого, останнього або n-го входження певного символу.

3.1 Видаліть текст до або після першого певного символу

Якщо ви хочете видалити текст до або після першого певного символу, такого як пробіл, кома зі списку текстових рядків, як показано на скріншоті нижче, тут я опублікую два способи для вас.

 Видаліть текст перед першим конкретним символом за допомогою формули

Щоб видалити текст або символи перед першим конкретним символом, ви можете створити формулу на основі функцій RIGHT, LEN і FIND, загальний синтаксис:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: Конкретний роздільник, на основі якого потрібно видалити текст.

Наприклад, щоб видалити все перед першою комою з рядків списку, ви повинні застосувати таку формулу до порожньої комірки, а потім перетягнути її вниз до потрібних клітинок, дивіться знімок екрана:

примітки:У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити текст; , Це конкретний символ, на основі якого ви хочете видалити текст. Ви можете змінити його на будь-які інші символи, якщо вам потрібно.

---

 Видаліть текст після першого певного символу за допомогою формули

Щоб видалити все після першого певного символу, ви можете використовувати функції LEFT і FIND, щоб отримати результат, загальний синтаксис:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: Конкретний роздільник, на основі якого потрібно видалити текст.

Тепер, будь ласка, введіть наведену нижче формулу в порожню клітинку, а потім перетягніть маркер заповнення до інших клітинок, де ви хочете застосувати цю формулу, і всі символи після першої коми будуть видалені одночасно, дивіться знімок екрана:

---

3.2 Видалити текст до або після N-го входження символу

Іноді текстові рядки містять кілька екземплярів певного роздільника, ви можете видалити всі символи до або після певного екземпляра, наприклад, другий, третій або четвертий, як вам потрібно. Щоб впоратися з цим типом видалення, можна скористатися наступними прийомами:

 Видалити текст перед N-м входженням символу з формулою

Щоб видалити текст перед N-м появою певного символу, вам може допомогти наступна формула, загальний синтаксис:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: конкретний роздільник, на основі якого ви хочете видалити текст;
• N: поява символу, перед яким потрібно видалити текст.

Наприклад, щоб видалити все перед другою комою з текстових рядків, слід застосувати наведену нижче формулу:

примітки:У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити текст; , це конкретний символ, на основі якого ви хочете видалити текст, ви можете змінити його на будь-які інші символи, як вам потрібно; 2 вказує на n-у кому, перед якою потрібно видалити текст.

А потім перетягніть маркер заповнення, щоб скопіювати формулу в інші клітинки, дивіться знімок екрана:

---

 Видалити текст після N-го входження символу з формулою

Щоб видалити текст після N-го входження певного роздільника, функції ЛІВО, ЗАМІНА і ЗНАЙТИ можуть зробити вам послугу. Загальний синтаксис:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: конкретний роздільник, на основі якого ви хочете видалити текст;
• N: поява символу, після якого потрібно видалити текст.

Коли ви зрозумієте основний синтаксис, скопіюйте або введіть формулу нижче в порожню клітинку:

примітки:У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити текст; , це конкретний символ, на основі якого ви хочете видалити текст, ви можете змінити його на будь-які інші символи, як вам потрібно; 2 вказує на n-у кому, після якої потрібно видалити текст.

Потім перетягніть маркер заповнення, щоб скопіювати формулу в інші клітинки, і всі символи після другої коми будуть видалені відразу, дивіться знімок екрана:

---

 Видаліть текст до або після N-го входження символу за допомогою функції, визначеної користувачем

Як бачите, ви можете вирішити випадки видалення тексту до або після N-го появи символу, використовуючи власні функції Excel в різних комбінаціях. Проблема в тому, що вам потрібно запам’ятати ці хитрі формули. У цьому випадку я створю визначену користувачем функцію, щоб охопити всі сценарії, будь ласка, зробіть так:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалити текст до або після N-го входження символу

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Потім закрийте та вийдіть із вікна коду, поверніться до робочого аркуша, використовуйте такі формули:

Видаліть текст перед другим входженням коми:

Видаліть текст після другого входження коми

---

3.3 Видалити текст до або після останнього входження символу

Якщо вам потрібно видалити весь текст до або після останнього певного символу і залишити лише підрядки після або перед останнім певним символом, як показано на знімку екрана нижче, у цьому розділі буде розказано про деякі формули для вирішення цієї проблеми.

 Видаліть текст перед останнім входженням символу з формулою

Щоб видалити всі символи перед останнім входженням символу, загальний синтаксис такий:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: конкретний роздільник, на основі якого ви хочете видалити текст;

Тепер, якщо вам потрібно видалити текст перед останнім входом коми, скопіюйте або введіть формулу нижче в порожню клітинку:

примітки:У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити текст; , Це конкретний символ, на основі якого ви хочете видалити текст. Ви можете змінити його на будь-які інші символи, якщо вам потрібно.

Потім перетягніть маркер заповнення, щоб скопіювати формулу в інші клітинки, і всі символи перед останньою комою будуть видалені, як показано нижче:

---

 Видаліть текст після останнього входження символу з формулами

Якщо значення клітинок розділені змінною кількістю роздільників, тепер ви хочете видалити все після останнього екземпляра цього роздільника, загальний синтаксис такий:

• осередок: посилання на клітинку або текстовий рядок, з якого потрібно видалити текст;
• бак: конкретний роздільник, на основі якого ви хочете видалити текст;

Скопіюйте або введіть наведену нижче формулу в порожню клітинку, а потім перетягніть маркер заповнення вниз, щоб отримати інші потрібні результати, дивіться знімок екрана:

примітки:У наведеній вище формулі: A2 це клітинка, з якої потрібно видалити текст; , Це конкретний символ, на основі якого ви хочете видалити текст. Ви можете змінити його на будь-які інші символи, якщо вам потрібно.

---

3.4 Видалити текст між дужками

Якщо у вас є список текстових рядків із частиною символів, укладеними в дужки, тепер ви можете видалити всі символи в дужках, включаючи самі дужки, як показано на знімку екрана нижче. У цьому розділі піде мова про деякі хитрощі для вирішення цього завдання в Excel.

 Видаліть текст між дужками за допомогою функції «Знайти та замінити».

У Excel вбудована функція «Знайти та замінити» може допомогти вам знайти всі тексти в дужках, а потім замінити їх нічим. Будь ласка, зробіть так:

1. Виберіть список даних, з якого потрібно видалити тексти між дужками.

2. Потім натисніть кнопку Головна > Знайти та вибрати > заміщати (або натисніть Ctrl + H, клавіші), щоб відкрити діалогове вікно «Знайти та замінити» у Знайти і замінити у діалоговому вікні виконайте такі операції:

• У Знайти те, що поле, тип (*) у текстове поле;
• У Замініть поле, залиште це поле порожнім.

3. Потім натисніть кнопку замінити всі кнопки, усі символи в дужках (включаючи дужки) у вибраних клітинках буде видалено відразу. Дивіться знімок екрана:

Tips : Знайти і замінити Функція також працює для двох або більше пар дужок у текстових рядках.

---

 Видаліть текст між дужками за допомогою формули

Окрім функції «Знайти та замінити», ви також можете використовувати формулу для вирішення цього завдання в Excel, загальний синтаксис:

• текст: текстовий рядок або посилання на клітинку, з яких потрібно видалити символи.

Тепер скопіюйте або введіть таку формулу в порожню клітинку, де ви хочете отримати результат:

Потім перетягніть маркер заповнення вниз до клітинок, де ви хочете застосувати цю формулу, і всі тексти в дужках, включаючи дужки, буде видалено відразу, дивіться знімок екрана:

Tips : Якщо у значенні комірки немає дужок, після застосування наведеної вище формули відобразиться помилка, щоб проігнорувати помилку, скористайтеся наведеною нижче формулою:

---

 Видаліть текст між дужками за допомогою функції, визначеної користувачем

Наведена вище формула добре працює для видалення тексту з однієї пари дужок, якщо вам потрібно видалити тексти з кількох пар дужок у текстових рядках, формула працюватиме некоректно. Тут я створю просту визначену користувачем функцію для вирішення цього завдання.

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалити текст між дужками

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Потім поверніться до робочого аркуша та введіть цю формулу в порожню клітинку: = ремтекст (A2), потім перетягніть маркер заповнення вниз до клітинок, де ви хочете застосувати цю формулу, усі тексти в усіх дужках, включаючи дужки, буде видалено, як показано нижче:

---

Видаліть слова з текстових рядків

У певному випадку ви можете видалити деякі слова зі списку клітинок, наприклад перше чи останнє слово, повторювані слова з клітинки. Щоб вирішити подібні види видалення, у цьому розділі будуть представлені деякі методи для вас.

4.1 Видалити перше або останнє слово з текстового рядка

Щоб видалити перше або останнє слово зі списку текстових рядків, наведені нижче формули можуть допомогти вам.

 Видаліть перше слово з текстового рядка за допомогою формули

Видаліть перші слова зі списку текстових рядків, ви можете створити просту формулу на основі функцій RIGHT, LEN і FIND, загальний синтаксис:

• текст: текстовий рядок або посилання на клітинку, з якого потрібно видалити перше слово.

Тепер, будь-ласка, введіть або скопіюйте наступну формулу в порожню комірку:

А потім перетягніть маркер заповнення вниз, щоб застосувати формулу до інших клітинок, дивіться знімок екрана:

Tips : Якщо вам потрібно видалити перші N слів з клітинок, скористайтеся наведеною нижче формулою:

• текст: текстовий рядок або посилання на клітинку, з яких потрібно видалити перші n слів;
• N: вказує, скільки слів потрібно видалити з початку текстового рядка.

Наприклад, щоб видалити перші два слова з клітинок, будь ласка, скопіюйте або введіть формулу нижче в порожню клітинку, щоб отримати необхідний результат, дивіться знімок екрана:

---

  Видаліть останнє слово з текстового рядка за допомогою формули

Щоб видалити останнє слово з текстових рядків, ви також можете використовувати формулу для вирішення цього завдання, загальний синтаксис:

• текст: текстовий рядок або посилання на клітинку, з якого потрібно видалити останнє слово;

Будь ласка, використовуйте формулу нижче в порожню клітинку, а потім перетягніть маркер заповнення вниз, щоб застосувати формулу до інших клітинок, дивіться знімок екрана:

Tips : Щоб видалити останні N слів зі списку комірок, загальний синтаксис:

• текст: текстовий рядок або посилання на клітинку, з якого потрібно видалити останні n слів;
• N: вказує кількість слів, які потрібно видалити з кінця текстового рядка.

Припустимо, щоб видалити останні 3 слова зі списку комірок, скористайтеся формулою нижче, щоб повернути результат, дивіться знімок екрана:

---

4.2 Видаліть повторювані символи або слова в клітинці

Під час видалення повторюваних значень або рядків Excel пропонує кілька різних варіантів, але коли справа доходить до видалення деяких повторюваних символів або слів у певній комірці, може не бути хорошої вбудованої функції для її вирішення. У цьому випадку цей розділ допоможе створити деякі визначені користувачем функції для вирішення цієї головоломки.

 Видаліть повторювані символи в клітинці за допомогою функції, визначеної користувачем

Якщо у вас є кілька входжень одного і того ж символу в клітинку, щоб видалити повторювані символи в клітинці та зберегти лише перші, як показано на знімку екрана, ви можете використовувати наступну функцію, визначену користувачем.

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалення повторюваних символів у клітинці

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Потім закрийте вікно коду, поверніться до робочого аркуша та введіть цю формулу =Видалити Dupechars(A2) у порожню клітинку, окрім ваших даних, а потім перетягніть маркер заповнення до клітинок, до яких ви хочете застосувати цю формулу, дивіться знімок екрана:

примітки: A2 це клітинка даних, з якої потрібно видалити повторювані символи.

Чайові: Функція чутлива до регістру, тому розглядає малі та великі літери як різні символи.

---

 Видаліть повторювані слова в клітинці за допомогою функції, визначеної користувачем

Припустимо, що у вас є ті самі слова або текстові рядки в клітинці, і ви хочете видалити всі ті самі слова з комірки, як показано на знімку екрана нижче. Щоб розв’язати це завдання в Excel, можна використовувати наведену нижче функцію.

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: видалення повторюваних слів у клітинці

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Потім закрийте вікно коду, поверніться до робочого аркуша та введіть цю формулу =ВидалитиDupeswords(A2,", ") у порожню клітинку, окрім ваших даних, а потім перетягніть маркер заповнення до клітинок, до яких ви хочете застосувати цю формулу, дивіться знімок екрана:

примітки: A2 це клітинка, з якої потрібно видалити повторювані слова, а також кома та пробіл (, ) є роздільниками для розділення текстових рядків, ви можете змінити їх на будь-які інші роздільники відповідно до ваших потреб.

Чайові: Ця функція не чутлива до регістру, малі та великі літери розглядаються як однакові символи.

---

4.3 Обрізати текстовий рядок до N слів

Якщо у вас є довгий текстовий рядок у клітинці, іноді ви можете обрізати текстовий рядок до певної кількості слів, що означає зберегти лише перші n слів, а інші слова вирізати. У цьому розділі буде розказано про деякі хитрощі, які допоможуть вам виконати цю роботу в Excel.

 Обріжте текстовий рядок до N слів за допомогою формули

Щоб обрізати текстовий рядок до N слів, ви можете створити формулу на основі функцій LEFT, FIND і SUBSTITUTE, загальний синтаксис:

• текст: текстовий рядок або посилання на клітинку, які потрібно обрізати;
• N: кількість слів, які ви хочете зберегти з лівого боку даного текстового рядка.

Щоб виконати цю роботу, будь ласка, скопіюйте або введіть формулу нижче в порожню клітинку:

А потім перетягніть маркер заповнення вниз, щоб застосувати цю формулу до інших клітинок, дивіться знімок екрана:

---

 Обріжте текстовий рядок до N слів за допомогою функції, що визначає користувач

Крім наведеної вище формули, ви також можете створити визначену користувачем функцію для вирішення цього завдання, будь ласка, зробіть наступне:

1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, і вставте наступний код у вікно модуля.

Код VBA: обрізати текстовий рядок до N слів

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Потім закрийте та вийдіть із вікна коду, поверніться до робочого аркуша та введіть цю формулу: =ОтриматиNWords(A2,B2) у порожню клітинку, а потім перетягніть маркер заповнення вниз, щоб застосувати цю формулу до інших клітинок, зберігається лише перша конкретна кількість слів, як показано нижче: