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

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

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

В статье Как удалить символы в ячейке Excel мы рассмотрели, как удалить отдельные буквы либо целые слова. Здесь мы разберем отдельные случаи с нестандартными символами.

Как удалить определенный символ из ячейки Excel

Чтобы удалить определенный лишний символ из ячейки, замените его пустой строкой, используя функцию ПОДСТАВИТЬ:

ПОДСТАВИТЬ( ячейка ; символ ; "")

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

=ПОДСТАВИТЬ(A2; "©"; "") 

Как удалить определенный лишний символ из ячейки Excel

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

Но если нежелательный символ невидим или копируется неправильно, как его поместить в формулу? 

Просто найдите его кодовый номер с помощью функции КОДСИМВ.

Функция КОДСИМВ имеет очень простой синтаксис:

=КОДСИМВ( символ )

Она возвращает код этого символа в таблице ASCII.

В нашем случае лишний символ («©») является последним в ячейке A2, поэтому мы используем комбинацию функций КОДСИМВ и ПРАВСИМВ, чтобы получить его уникальное значение кода, равное 169:

=КОДСИМВ(ПРАВСИМВ(A2))

как определить код символа в Excel

Как только вы получите код символа, используйте функцию СИМВОЛ в приведенной выше общей формуле, чтобы вставить в нее этот значок. Для нашего примера данных формула выглядит следующим образом:

=ПОДСТАВИТЬ(A2; СИМВОЛ(169); "")

Результат вы видите на скриншоте ниже.

как удалить символ с определенным кодом в Excel

Примечание. Функция ПОДСТАВИТЬ учитывает регистр, то есть строчные и прописные буквы воспринимаются как разные символы. Имейте это в виду, если ваш лишний символ, который вы хотите удалить — буква.

Удалить несколько лишних символов из текста

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

ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ячейка ; символ1 ; ""); символ2 ; ""); символ3 ; "")

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; "!"; "");"|"; ""); "?"; ""); "©"; "")

То же самое можно сделать с помощью функции СИМВОЛ, где 169 — код значка «©»:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; "!"; "");"|"; ""); "?"; ""); СИМВОЛ(169); "")

как удалить несколько лишних символов из текста

Вложенные функции ПОДСТАВИТЬ хорошо работают для небольшого количества символов. Но если вам нужно удалить десяток разных символов, формула становится слишком длинной и сложной, да и корректировать ее без ошибок не так просто. Следующий пример демонстрирует более компактное решение.

Удаление специальных символов с помощью VBA

Функции работают во всех версиях Excel.

Вы можете создать специальную функцию для удаления нежелательных символов с помощью VBA. Пользовательскую функцию (UDF) можно записать двумя способами.

Пользовательская функция для удаления специальных символов :

Function RemoveUnwantedChars(str As String, chars As String)
  If ("" <> chars) Then
    str = Replace(str, Left(chars, 1), "")
    chars = Right(chars, Len(chars) - 1)
    RemoveUnwantedChars = RemoveUnwantedChars(str, chars)
  Else
    RemoveUnwantedChars = str
  End If
End Function

Здесь нежелательные символы заменяются пустотой (пустой строкой), что равнозначно их удалению.

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

Function RemoveUnwantedChars(str As String, chars As String)
  For index = 1 To Len(chars)
    str = Replace(str, Mid(chars, index, 1), "")
  Next
  RemoveUnwantedChars = str
End Function

Вставьте один из приведенных выше кодов в свою книгу, как описано в разделе Как вставить код VBA в Excel , и ваша пользовательская функция готова к использованию.

RemoveUnwantedChars(текст ; символы)

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

=RemoveUnwantedChars(A3; $D$3)

удалить ненужные символы из ячейки Excel

Пользовательская функция с жестко запрограммированными символами

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

Function RemoveSpecialChars(str As String) As String
  Dim chars As String
  Dim index As Long

  chars = "?¿!¡*%#$(){}[]^&/\~+-"
  For index = 1 To Len(chars)
    str = Replace(str, Mid(chars, index, 1), "")
  Next
  RemoveSpecialChars = str
End Function

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

chars = "?¿!¡*%#$(){}[]^&/\~+-"

Эта пользовательская функция называется RemoveSpecialChars и требует всего один аргумент — ячейку с данными:

RemoveSpecialChars(текст)

Чтобы удалить лишние символы из нашего набора данных, используйте формулу:

=RemoveSpecialChars(A3)

Удаление непечатаемых символов в Excel

Зачем удалять непечатаемые символы? Ведь они не видны и вроде бы никак нам не мешают. Однако, если вы, к примеру, будете производить поиск наименования товара при помощи формулы ВПР, чтобы получить его цену, то просто не найдете нужное наименование. Ведь в нем есть невидимый для вас «лишний» символ. 

В Microsoft Excel есть специальная функция для удаления непечатаемых символов — функция ПЕЧСИМВ (CLEAN в английской версии). Технически она удаляет первые 32 символа 7-битного набора ASCII (коды от 0 до 31).

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

=ПЕЧСИМВ(A2)

Это позволит устранить непечатаемые символы, но пробелы до/после текста и между словами останутся.

Чтобы избавиться от лишних пробелов, оберните формулу ПЕЧСИМВ в функцию СЖПРОБЕЛЫ :

=СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))

Теперь все начальные и конечные пробелы удалены, а промежуточные сокращены до одного пробела:

Если вы хотите удалить абсолютно все пробелы внутри текста, то дополнительно замените символ пробела (кодовый номер 32) пустой строкой:

=СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(32);""))))

Еще несколько полезных примеров использования функции ПЕЧСИМВ вы можете увидеть здесь: Как убрать разрыв строки и непечатаемые символы.

На вашем листе все еще остались пробелы или другие невидимые символы? Это означает, что эти символы имеют разные значения в наборе символов Юникода.

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

=ПОДСТАВИТЬ(A2; СИМВОЛ(160);" ")

Как удалить определенный непечатаемый символ

Если комбинация трех функций, описанных в приведенном выше примере (ПЕЧСИМВ, СЖПРОБЕЛЫ и ПОДСТАВИТЬ), не смогла удалить пробелы или непечатаемые символы на вашем листе, это означает, что эти символы имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).

В этом случае используйте функцию КОДСИМВ, чтобы определить кодовое значение символа, а затем используйте ПОДСТАВИТЬ, чтобы заменить его обычным пробелом, и СЖПРОБЕЛЫ, чтобы удалить этот появившийся лишний пробел.

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

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

=КОДСИМВ(ЛЕВСИМВ(A2;1))

  • Завершающий пробел или непечатаемый символ в конце строки:

=КОД(ПРАВСИМВ(A2;1))

  • Пробел или непечатаемый символ в середине строки, где n — позиция проблемного символа:

=КОДСИМВ(ПСТР(A2; n; 1)))

  1. Например, у нас есть какой-то неизвестный непечатаемый символ в середине текста, на 4-й позиции, и мы извлекаем его при помощи функции ПСТР и узнаем его код по такой формуле:

=КОДСИМВ(ПСТР(A2;4;1))

  1. Функция КОД возвращает значение 127.
  2. В ячейке C2 вы заменяете СИМВОЛ(127) обычным пробелом (" "), а затем удаляете этот пробел:

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2; СИМВОЛ(127); " "))

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

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(127);" ");СИМВОЛ(160);" "))

Как удалить определенный непечатаемый символ

Удаление специальных символов с помощью Ultimate Suite

Поддерживает Excel для Microsoft 365, Excel 2019–2010.

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

  1. На вкладке « Ablebits Data» в группе «Text» нажмите «Удалить» > «Удалить символы» .
  1. На панели надстройки выберите исходный диапазон, выберите «Удалить наборы символов» и выберите нужный вариант из раскрывающегося списка ( в данном примере — символы и знаки препинания ).
  1. Нажмите кнопку «Удалить» .

Через мгновение вы получите идеальный результат:

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

Более подробную информацию смотрите здесь: 8 инструментов для работы с текстовыми данными.

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