Excel удаляет символы, слова, числа из текстовых строк

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

---

Удалите символы слева, справа или в середине текстовых строк

Для большинства из нас может быть обычной задачей удалить некоторые символы слева, справа или в середине текстовых строк на листах Excel. В этом разделе представлены несколько простых и быстрых приемов решения этой задачи.

1.1 Удалить первые n символов из текстовых строк

Если вам нужно удалить первые n символов из списка текстовых строк, следующие методы могут оказать вам услугу.

 Используя формулы

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

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

• string: Текстовая строка, из которой вы хотите удалить символы;
• число_символов: Количество символов, которые вы хотите удалить.

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

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

• string: Текстовая строка, из которой вы хотите удалить символы;
• число_символов: Количество символов, которые вы хотите удалить.

Чтобы удалить первые 2 символа из ячеек, примените следующую формулу:

---

 Используя функцию, определяемую пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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 символов:

• string: Текстовая строка, из которой вы хотите удалить символы;
• число_символов: Количество символов, которые вы хотите удалить.

Чтобы удалить 3 символа из конца текстовых строк, используйте эту формулу, а затем перетащите маркер заполнения, чтобы скопировать формулу в другие ячейки, см. Снимок экрана:

---

 Используя функцию, определяемую пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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 for Excel поддерживает мощную функцию - Удалить по позиции. С помощью этого небольшого инструмента вы можете справиться с этими задачами несколькими щелчками мыши, не запоминая никаких формул.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выделите ячейки, в которых нужно удалить символы, и нажмите Кутулс > Текст > Удалить по позиции, см. снимок экрана:

2. В Удалить по позиции диалоговом окне выполните следующие действия:

2.1 Удалите первые n символов из ячеек:

• In Номера текстовое поле введите количество символов, которые вы хотите удалить из строк. В этом примере я удалю первые 2 символа.
• Выберите Слева вариант в Позиция .
• Затем нажмите Ok or Применить кнопку, чтобы получить результат, как показано на скриншоте ниже.

2.2 Удалить последние n символов из ячеек:

• In Номера текстовое поле введите количество символов, которые вы хотите удалить из строк. В этом примере я удалю последние 3 символа.
• Выберите Справа вариант в Позиция .
• Затем нажмите Ok or Применить кнопку, чтобы получить результат, как показано на скриншоте ниже.

2.3 Удалить n символов из определенных позиций ячеек:

Если вам нужно удалить определенное количество символов из определенной позиции текстовых строк, например, удаление 3 символов начинается с третьего символа строк.

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

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

1.4 Удалите первые n и последние n символов из текстовых строк с помощью формулы

Когда вам нужно удалить некоторые символы с обеих сторон текстовых строк в Excel, вы можете объединить функции MID и LEN, чтобы создать формулу для решения этой задачи.

• string: Текстовая строка, из которой вы хотите удалить символы;
• left_chars: Количество символов, которые нужно удалить слева;
• right_chars: Количество символов, которые нужно удалить справа.

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

Внимание: В этой формуле: A4 это ячейка, из которой вы хотите удалить символы; Номер 7 это количество символов, которое вы хотите удалить с левой стороны; Номер 5 - это количество символов, которое вы хотите удалить с правой стороны.

Затем перетащите маркер заполнения туда, где вы хотите применить эту формулу, и вы получите результат, как показано на скриншоте ниже:

---

Удалите ненужные / специальные символы из текстовых строк

При импорте данных из другого места в Excel на ваш рабочий лист может быть вставлено множество специальных или нежелательных символов. Чтобы удалить эти нежелательные символы, такие как # @ $% ^ &, пробелы, числа, нечисловые числа, разрывы строк и т. Д., В этом разделе будут представлены несколько полезных методов, которые могут вам помочь.

2.1 Удаление некоторых специальных символов из текстовых строк

Если в текстовых строках есть специальные символы, такие как% ^ & * (), чтобы удалить этот тип символов, вы можете применить следующие три уловки.

 Удалите несколько специальных символов из текстовых строк с помощью функции ЗАМЕНА

Обычно в Excel вы можете вложить несколько функций ЗАМЕНЫ, чтобы ничего не заменять каждый конкретный символ, общий синтаксис следующий:

• строка_ячейка: Ячейка содержит текстовую строку, из которой вы хотите удалить специальные символы;
• символ1, символ2, символ3: Нежелательные символы, которые вы хотите удалить.

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

Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все указанные вами нежелательные символы будут сразу удалены, см. Снимок экрана:

Советы: Если есть другие символы, которые вы хотите удалить, вам просто нужно вложить больше функций ЗАМЕНА в формулу.

---

 Удалите несколько специальных символов из текстовых строк с помощью функции, определяемой пользователем

Вышеупомянутые вложенные функции ЗАМЕНА хорошо работают, если нужно удалить несколько специальных символов, но если вам нужно удалить десятки символов, формула станет слишком длинной и сложной для управления. В этом случае следующая функция, определяемая пользователем, может помочь вам быстро и легко выполнить эту задачу.

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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. Затем закройте окно кода и вернитесь на рабочий лист, введите эту формулу = RemoveUnwantedChars (A2; $ D $ 2) в пустую ячейку, куда нужно вывести результат, а затем перетащите дескриптор заполнения вниз, чтобы получить нужные результаты, см. снимок экрана:

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

---

 Удалите несколько специальных символов из текстовых строк с помощью удивительной функции

Если вы установили Kutools for Excel, С его Удалить символы функция, вы можете удалить все виды символов, такие как числовые символы, буквенные символы, непечатаемые символы ... из списка ячеек по мере необходимости.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон ячеек, из которого вы хотите удалить специальные символы, а затем нажмите Кутулс > Текст > Удалить символы, см. снимок экрана:

2. В Удалить символы диалоговое окно:

• Проверьте На заказ вариант под Удалить символы .
• Затем введите специальные символы в текстовое поле, которое вы хотите удалить.
• Затем нажмите Ok or Применить кнопку, чтобы сразу удалить указанные вами символы. Смотрите скриншот:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

2.2 Удалить все числа из текстовых строк

Если у вас есть список текстовых строк, которые смешиваются с числами, буквами и специальными символами, и теперь вы просто хотите удалить все числа и оставить другие символы. В этом разделе вы найдете несколько полезных способов помочь вам.

 Удалите числа из текстовых строк с помощью функции ЗАМЕНА

В Excel вложенная функция ЗАМЕНА может помочь заменить все числа ничем, поэтому вы можете использовать приведенную ниже формулу для удаления всех чисел из ячеек:

Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все числа будут удалены из списка текстовых строк, см. Снимок экрана:

---

 Удалите числа из текстовых строк с помощью функции TEXTJOIN

Если у вас есть Excel 2019, 2021 или 365, новая функция TEXTJOIN также может помочь удалить числа из текстовых строк.

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

А затем скопируйте формулу в другие ячейки ниже, где вы хотите применить эту формулу, см. Снимок экрана:

Внимание: Этот TEXTJOIN доступен только в Excel 2019, 2021 и Office 365.

---

 Удаление чисел из текстовых строк с помощью функции, определяемой пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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 for ExcelАвтора Удалить символы. С помощью этой удобной функции вы можете выполнить эту задачу всего за несколько щелчков мышью.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

2. В Удалить символы диалоговом окне выполните следующие действия:

• Проверьте Числовой вариант под Удалить символы .
• Затем нажмите Ok or Применить кнопку, чтобы немедленно удалить числа. Смотрите скриншот:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

2.3 Удаление нечисловых символов из текстовых строк

Чтобы удалить все нечисловые символы и сохранить только числа из текстовых строк, в этом разделе будут рассказаны о некоторых способах решения этой задачи в Excel.

 Удалите нечисловые символы из текстовых строк с формулой в Excel 2016 и более ранних версиях

Если вы используете Excel 2016 или более ранние версии, вам следует применить сложную формулу для выполнения этой задачи, скопируйте или введите приведенную ниже формулу в пустую ячейку:

А затем скопируйте формулу в другие ячейки ниже, где вы хотите применить эту формулу, см. Снимок экрана:

Внимание: Если числа в текстовой строке начинаются с 0, 0 будет потерян.

---

 Удалите нечисловые символы из текстовых строк с помощью функции TEXTJOIN в Excel 2019, 2021, 365

Приведенная выше формула может быть слишком сложной для понимания большинством из нас. Если у вас есть Excel 2019, 2021 или 365, есть отличная формула, которая может вам помочь.

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

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

Внимание: С помощью этой формулы вы можете видеть, что ведущие нули будут сохранены, поскольку числа возвращаются в виде текста.

---

 Удалите нечисловые символы из текстовых строк с помощью функции, определяемой пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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 for ExcelАвтора Удалить символы Утилита может сделать это всего за несколько кликов.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон ячеек, из которого вы хотите удалить нечисловые символы, а затем щелкните Кутулс > Текст > Удалить символы.

2. В Удалить символы диалоговое окно, установите следующие операции:

• Проверьте Нечисловой вариант под Удалить символы .
• Затем нажмите Ok or Применить кнопку, чтобы немедленно удалить все нечисловые символы. Смотрите скриншот:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

2.4. Разделите текст и числа из одной ячейки на два столбца

Иногда вам может потребоваться извлечь текст и числа из текстовых строк в два отдельных столбца. С помощью следующих методов вы можете быстро и легко выполнить эту задачу.

 Разделите текст и числа из одной ячейки на два столбца с помощью функции, определяемой пользователем

Используя следующую функцию, определяемую пользователем, вы можете сразу извлекать текст и числа, выполните следующие действия:

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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; ЛОЖЬ) в пустую ячейку, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, чтобы получить весь текст, см. снимок экрана:

4. А затем продолжайте вводить эту формулу: = SplitText (A2; ИСТИНА) в другую ячейку и перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, чтобы получить числа, см. снимок экрана:

---

 Разделите текст и числа из одной ячейки на два столбца с помощью простой функции

Если вы только что Kutools for Excel, Его Разделить клетки Утилита может помочь вам разделить ячейки на несколько столбцов или строк на основе любых разделителей, заданной ширины или текста и числа.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон ячеек, который вы хотите разделить, и нажмите Кутулс > Слияние и разделение > Разделить клетки, см. снимок экрана:

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

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

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

2.5 Удаление символов разрыва строки из текстовых строк

Разрыв строки - это то, что позволяет вам иметь несколько строк в одной ячейке в Excel. Иногда, когда вы копируете данные с веб-сайта или разделяете содержимое ячейки с помощью Alt + Enter вручную, вы получите перевод строки или возврат каретки. В некоторых случаях вы можете удалить разрывы строк, чтобы сделать содержимое ячейки одной строкой, как показано на скриншоте ниже. Здесь я расскажу о некоторых способах решения этой задачи в Excel.

 Удалите символы разрыва строки из текстовых строк с помощью функции поиска и замены

В Excel вы можете использовать Найти и заменить для удаления разрывов строк, сделайте следующее:

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

2, Затем нажмите Главная > Найти и выбрать > Замените (или нажмите Ctrl + H ключи), чтобы перейти Найти и заменить диалоговое окно, см. снимок экрана:

3. В выскочившем Найти и заменить диалоговом окне выполните следующие действия:

• Поместите курсор в Найти то, что поле и нажмите Ctrl + J на клавиатуре вы можете ничего не увидеть, но вставлен символ разрыва строки.
• В Заменить оставьте это поле пустым, чтобы просто удалить разрывы строк, или нажмите клавишу Пробел баr один раз, чтобы заменить разрывы строк пробелами.

4, Затем нажмите Заменить все при нажатии кнопки все разрывы строк в выбранных ячейках будут сразу удалены или заменены пробелами. Смотрите скриншот:

---

 Удалите символы разрыва строки из текстовых строк с помощью функции ЗАМЕНА

Вы также можете создать формулу на основе функций ПОДСТАВИТЬ и СИМВОЛ, чтобы удалить разрывы строк из текстовых строк.

Пожалуйста, примените формулу ниже, чтобы получить результат:

Советы: Функция ПОДСТАВИТЬ находит и заменяет символ CHAR (10), который представляет символ разрыва строки, ничем. Если вы хотите, чтобы результат был разделен запятой и пробелом, вы можете использовать следующую формулу:

---

 Удалите символы разрыва строки из текстовых строк с помощью кода VBA

Если вам удобно использовать код VBA, здесь вы также найдете код, выполните следующие действия:

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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 for ExcelАвтора Удалить символы функция также может помочь вам с легкостью удалять разрывы строк.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

2. В Удалить символы диалоговое окно, установите следующие операции:

• Проверьте Непечатаемый вариант под Удалить символы .
• Затем нажмите Ok or Применить кнопку, чтобы удалить все разрывы строк из выбранного диапазона данных. Смотрите скриншот:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

2.6 Удаление пробелов (ведущих, конечных, дополнительных или всех) из текстовых строк

Копирование и вставка текста из внешнего источника в рабочий лист Excel часто приводит к появлению некоторых раздражающих оставшихся пробелов, удаление начальных, конечных или других лишних пробелов вручную будет утомительным занятием. К счастью, в Excel есть несколько простых приемов для решения этой задачи.

 Удалите лишние пробелы (ведущие, конечные, лишние) из текстовых строк с помощью функции TRIM

В Excel для удаления начальных, конечных и дополнительных пробелов из текстовых строк вам может помочь простая функция TRIM. Эта функция удаляет все пробелы, кроме одинарных пробелов между словами.

Введите следующую формулу в пустую ячейку:

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

---

 Удалить все пробелы из текстовых строк

Если вы хотите удалить все пробелы из текстовых строк, следующая функция ЗАМЕНА и функция поиска и замены могут оказать вам услугу.

Используя функцию ЗАМЕНА

Вы можете использовать функцию ЗАМЕНА, чтобы заменить все пробелы ничем, примените приведенную ниже формулу в пустую ячейку:

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

---

Используя функцию "Найти и заменить"

В самом деле, Найти и заменить Функция в Excel также может помочь избавиться от всех пробелов в выбранных ячейках, выполните следующие действия:

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

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

• Нажмите Пробел в Найти то, что поле;
• В Заменить поле, оставьте это поле пустым.

3. А затем нажмите Заменить все кнопка, все пробелы в выбранных ячейках будут удалены сразу. Смотрите скриншот:

---

 Удалите все виды пробелов из текстовых строк с помощью мощной функции

Kutools for Excel имеет мощную особенность - Удалить пробелыс помощью этой утилиты вы можете не только удалить начальные, конечные и лишние пробелы, но и все пробелы из выбранных диапазонов в одном диалоговом окне, что повысит продуктивность вашей работы.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, из которого вы удалите пробелы, а затем щелкните Кутулс > Текст > Удалить пробелы. Смотрите скриншот:

2. В Удалить пробелы диалоговом окне выберите один тип пространства, которое вы хотите удалить из Тип пространства:

• Удалите начальные пробелы, выберите Ведущие места вариант;
• Удалите конечные пробелы, выберите Конечные пробелы вариант;
• Удалите начальные и конечные пробелы сразу, пожалуйста, выберите Начальные и конечные пробелы вариант;
• Удалите все лишние пробелы, выберите Все лишние места вариант;
• Удалите все пробелы, выберите Все пространства опцию.

3, Затем нажмите Ok or Применить кнопку, вы получите нужный результат.

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

---

Удалить символы / текст до или после определенного символа

В этом разделе я представлю некоторые операции по удалению текста или символов до или после первого, последнего или n-го появления определенного символа.

3.1 Удаление текста до или после первого конкретного символа

Если вы хотите удалить текст до или после первого определенного символа, такого как пробел, запятая, из списка текстовых строк, как показано на скриншоте ниже, здесь я опубликую для вас два метода.

 Удалить текст перед первым конкретным символом с помощью формулы

Чтобы удалить текст или символы перед первым конкретным символом, вы можете создать формулу на основе функций RIGHT, LEN и FIND, общий синтаксис:

• ячейка: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст;
• колесница: Конкретный разделитель, на основе которого вы хотите удалить текст.

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

Внимание: В приведенной выше формуле: A2 это ячейка, из которой вы хотите удалить текст; , - это конкретный символ, на основе которого вы хотите удалить текст, вы можете изменить его на любые другие символы по своему усмотрению.

---

 Удалить текст после первого определенного символа с помощью формулы

Чтобы удалить все, что находится после первого определенного символа, вы можете использовать функции LEFT и FIND для получения результата, общий синтаксис:

• ячейка: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст;
• колесница: Конкретный разделитель, на основе которого вы хотите удалить текст.

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

---

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

Иногда текстовые строки содержат несколько экземпляров определенного разделителя, вы можете удалить все символы до или после определенного экземпляра, например, второй, третий или четвертый по мере необходимости. Чтобы справиться с этим типом удаления, вы можете использовать следующие приемы:

 Удалить текст перед N-м вхождением символа с формулой

Чтобы удалить текст перед N-м вхождением определенного символа, вам может помочь следующая формула, общий синтаксис:

• ячейка: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст;
• колесница: Конкретный разделитель, на основе которого вы хотите удалить текст;
• N: Вхождение символа, перед которым следует удалить текст.

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

Внимание: В приведенной выше формуле: A2 это ячейка, из которой вы хотите удалить текст; , это конкретный символ, на основе которого вы хотите удалить текст, вы можете изменить его на любые другие символы по своему усмотрению; 2 указывает n-ю запятую, перед которой вы хотите удалить текст.

Затем перетащите маркер заполнения, чтобы скопировать формулу в другие ячейки, см. Снимок экрана:

---

 Удалить текст после N-го вхождения символа с формулой

Чтобы удалить текст после N-го вхождения определенного разделителя, функции LEFT, SUBSTITUTE и FIND могут оказать вам услугу. Общий синтаксис:

• ячейка: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст;
• колесница: Конкретный разделитель, на основе которого вы хотите удалить текст;
• N: Появление символа, после которого следует удалить текст.

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

Внимание: В приведенной выше формуле: A2 это ячейка, из которой вы хотите удалить текст; , это конкретный символ, на основе которого вы хотите удалить текст, вы можете изменить его на любые другие символы по своему усмотрению; 2 указывает n-ю запятую, после которой вы хотите удалить текст.

Затем перетащите маркер заполнения, чтобы скопировать формулу в другие ячейки, и все символы после второй запятой будут удалены сразу, см. Снимок экрана:

---

 Удаление текста до или после N-го символа с помощью функции, определяемой пользователем

Как видите, вы можете решить случаи удаления текста до или после N-го вхождения символа, используя собственные функции Excel в различных комбинациях. Проблема в том, что вам нужно запомнить эти хитрые формулы. В этом случае я создам функцию, определяемую пользователем, чтобы охватить все сценарии, пожалуйста, сделайте следующее:

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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, Затем нажмите Заменить все кнопки, все символы в круглых скобках (включая скобки) в выбранных ячейках будут удалены сразу. Смотрите скриншот:

Советы: Найти и заменить функция также работает для двух или более пар круглых скобок в текстовых строках.

---

 Удалить текст между скобками с формулой

Помимо функции «Найти и заменить», вы также можете использовать формулу для решения этой задачи в Excel, общий синтаксис:

• текст: Текстовая строка или ссылка на ячейку, из которых вы хотите удалить символы.

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

Затем перетащите дескриптор заполнения вниз к ячейкам, в которых вы хотите применить эту формулу, и все тексты в круглых скобках, включая скобки, будут сразу удалены, см. Снимок экрана:

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

---

 Удалите текст между скобками с помощью функции, определяемой пользователем

Вышеупомянутая формула хорошо подходит для удаления текста из одной пары круглых скобок, если вам нужно удалить текст из нескольких пар круглых скобок в текстовых строках, формула не будет работать правильно. Здесь я создам простую функцию, определяемую пользователем, для решения этой задачи.

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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. Затем вернитесь к рабочему листу и введите эту формулу в пустую ячейку: = remtxt (A2), затем перетащите дескриптор заполнения вниз к ячейкам, в которых вы хотите применить эту формулу, весь текст во всех круглых скобках, включая круглые скобки, будет удален, как показано на скриншоте ниже:

---

Удалить слова из текстовых строк

В некоторых случаях вы можете захотеть удалить некоторые слова из списка ячеек, например, первое или последнее слово, повторяющиеся слова из ячейки. В этом разделе мы расскажем о некоторых методах решения проблемы такого удаления.

4.1 Удалить первое или последнее слово из текстовой строки

Чтобы удалить первое или последнее слово из списка текстовых строк, вам могут помочь следующие формулы.

 Удалить первое слово из текстовой строки с помощью формулы

Удалите первые слова из списка текстовых строк, вы можете создать простую формулу на основе функций RIGHT, LEN и FIND, общий синтаксис:

• текст: Текстовая строка или ссылка на ячейку, из которой вы хотите удалить первое слово.

Теперь введите или скопируйте следующую формулу в пустую ячейку:

Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам, см. Снимок экрана:

Советы: Если вам нужно удалить первые N слов из ячеек, используйте следующую формулу:

• текст: Текстовая строка или ссылка на ячейку, из которой вы хотите удалить первые n слов;
• N: Указывает, сколько слов вы хотите удалить из начала текстовой строки.

Например, чтобы удалить первые два слова из ячеек, скопируйте или введите приведенную ниже формулу в пустую ячейку, чтобы получить нужный результат, см. Снимок экрана:

---

  Удалить последнее слово из текстовой строки с помощью формулы

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

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

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

Советы: Чтобы удалить последние N слов из списка ячеек, общий синтаксис:

• текст: Текстовая строка или ссылка на ячейку, из которой вы хотите удалить последние n слов;
• N: Указывает количество слов, которые вы хотите удалить из конца текстовой строки.

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

---

4.2 Удаление повторяющихся символов или слов в ячейке

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

 Удалите повторяющиеся символы в ячейке с помощью функции, определяемой пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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. Затем закройте окно кода, вернитесь на рабочий лист и введите эту формулу = RemoveDupeschars (A2) в пустую ячейку, кроме ваших данных, а затем перетащите маркер заполнения в ячейки, которые вы хотите применить к этой формуле, см. снимок экрана:

Внимание: A2 это ячейка данных, из которой вы хотите удалить повторяющиеся символы.

Функции: Функция чувствительна к регистру, поэтому строчные и прописные буквы рассматриваются как разные символы.

---

 Удалите повторяющиеся слова в ячейке с помощью функции, определяемой пользователем

Предположим, у вас есть те же слова или текстовые строки в ячейке, и вы хотите удалить все те же слова из ячейки, как показано на скриншоте ниже. Вы можете использовать следующую функцию, определяемую пользователем, для решения этой задачи в Excel.

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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. Затем закройте окно кода, вернитесь на рабочий лист и введите эту формулу = RemoveDupeswords (A2; ",") в пустую ячейку, кроме ваших данных, а затем перетащите маркер заполнения в ячейки, которые вы хотите применить к этой формуле, см. снимок экрана:

Внимание: A2 это ячейка, из которой вы хотите удалить повторяющиеся слова, а также запятую и пробел (, ) являются разделителями для разделения текстовых строк, вы можете изменить их на любые другие разделители по своему усмотрению.

Функции: Эта функция не чувствительна к регистру, строчные и прописные буквы обрабатываются как одни и те же символы.

---

4.3 Обрезать текстовую строку до N слов

Если у вас есть длинная текстовая строка в ячейке, иногда вам может потребоваться обрезать текстовую строку до определенного количества слов, что означает оставить только первые n слов и вырезать остальные слова. В этом разделе мы расскажем о некоторых приемах, которые помогут вам выполнить эту работу в Excel.

 Обрезать текстовую строку до N слов с помощью формулы

Чтобы обрезать текстовую строку до N слов, вы можете создать формулу на основе функций LEFT, FIND и SUBSTITUTE, общий синтаксис:

• текст: Текстовая строка или ссылка на ячейку, которую вы хотите обрезать;
• N: Количество слов, которые вы хотите сохранить слева от данной текстовой строки.

Чтобы выполнить это задание, скопируйте или введите приведенную ниже формулу в пустую ячейку:

Затем перетащите маркер заполнения вниз, чтобы применить эту формулу к другим ячейкам, см. Снимок экрана:

---

 Обрезать текстовую строку до N слов с помощью функции, определяемой пользователем

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

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код 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. Затем закройте и выйдите из окна кода, вернитесь на рабочий лист и введите эту формулу: = GetNWords (A2; B2) в пустую ячейку, а затем перетащите дескриптор заполнения вниз, чтобы применить эту формулу к другим ячейкам, сохраняется только первое определенное количество слов, как показано ниже: