Как в экселе убрать лишние символы из ячейки
Зачистка текста
180395 28.01.2014 Скачать пример
Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:
- лишние пробелы перед, после или между словами (для красоты!)
- ненужные символы ("г." перед названием города)
- невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
- апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)
Давайте рассмотрим способы избавления от такого "мусора".
Замена
"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".
Вариантов решения два:
- Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H).
Затем нажать кнопку Заменить все (Replace All) для удаления. - Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Функция ПОДСТАВИТЬ
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:
- Текст в котором производим замену
- Старый текст – тот, который заменяем
- Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый
текстовым префиксом.
Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Sub Replace_Latin_to_Russian() Rus = "асекорхуАСЕНКМОРТХ" Eng = "acekopxyACEHKMOPTX" For Each cell In Selection For i = 1 To Len(cell) c1 = Mid(cell, i, 1) If c1 Like "[" & Eng & "]" Then c2 = Mid(Rus, InStr(1, Eng, c1), 1) cell.Value = Replace(cell, c1, c2) End If Next i Next cell End Sub
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Ссылки по теме
- Поиск символов латиницы в русском тексте
- Проверка текста на соответствие заданному шаблону (маске)
- Деление "слипшегося" текста из одного столбца на несколько
Как удалить символ в ячейке Excel? Найти и заменить, СЖПРОБЕЛЫ, ПСТР, ПЕЧСИМВ и ПОДСТАВИТЬ
Tags:=Подставить()Найти и заменитьПеренос строкиРабота с текстомУдалить лишний символ
Как правильно выровнять в Excel текст в ячейке?
Специальная вставка в Excel
При выгрузке из учетной программы, при скачивании данных из интернета или, если файл заполняется неопытным пользователем, случается, что в ячейках остаются лишние символы, пробелы, точки, знаки переноса строки и т.
п. Как быстро и просто удалить любой символ в ячейке Excel?
Самая распространенная ошибка, когда Excel не воспринимает текст как число. Об этом можно читать здесь. Способов как удалить символ в ячейке много, постараюсь рассказать о каждом подробнее.
Содержание
- 1. Найти и заменить символ в ячейке Excel
- 2. Удалить символ в ячейке Excel формулой
- 3. =СЖПРОБЕЛЫ()
- 4. Удалить первый символ
- 5. Функция =ПЕЧСИМВ()
- Похожие статьи
1. Найти и заменить символ в ячейке Excel
Если вкратце, то существует специальный инструмент во всех продуктах MS Office. Вызывается клавишами Ctrl+F (найти) или Ctrl+H (заменить). В первое поле можно вписать символ, который нужно удалить, во второе ничего не вписывайте.
Подробнее об этом инструменте написано в этой статье
2. Удалить символ в ячейке Excel формулой
Удалить любой символ можно прекрасной функцией =ПОДСТАВИТЬ().
Эта функция имеет следующую конструкцию =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)
текст — здесь указывается ячейка, где записан текст для изменения
старый_текст — что меняем
новый_текст — на что меняем
номер_вхождения — является необязательным.
Он предписывает Excel заменить только символы, начиная с определенного номера. Интересное дополнение
Представим, что вам нужно заменить все точки на запятые, получится формула:
=ПОДСТАВИТЬ(A1;".";",")
3. =СЖПРОБЕЛЫ()
Часто пробелы не видны невооруженным глазом («Иванов Иван «), нужно писать дополнительную проверку, чтобы найти их, в общем, предпринимать дополнительные действия. Проще всего воспользоваться формулой =СЖПРОБЕЛЫ(). Она убирает все задвоенные пробелы, а так же лишние пробелы в конце и начале текста в ячейке (станет «Иванов Иван») .
Но! Будьте внимательнее — формула удаляет стандартные пробелы. Если вы к примеру копировали текст с сайта — он может содержать символы похожие на пробелы, но функция =СЖПРОБЕЛЫ() не будет их удалять. В таком случае придется пользоваться возможностью Найти и заменить.
Но в 95% случаев сжпробелы помогает на «отлично».
Удаление пробелов я даже вынес в отдельную статью
4. Удалить первый символ
Часто необходимо удалить первый, несколько последних символов и так далее.
Как это сделать быстро и не вручную? Воспользуйтесь функцией ПСТР.
Очень подробно об этом написано здесь.
Например, у нас в ячейках задан порядковый номер сотрудника. Нужно удалить первые символы до точки и пробел.
Формула будет выглядеть следующим образом:
=ПСТР(A:A;ПОИСК(".";A:A)+2;ДЛСТР(A:A)-ПОИСК(".";A:A))Так же можно разделить текст по столбцам и разнести их в разные ячейки.
Пример
5. Функция =ПЕЧСИМВ()
Функция с одним аргументом очень похожа на СЖПРОБЕЛЫ, но она удаляет непечатные символы в строке, такие как перенос строки, знаки абзаца, какие-нибудь квадратики и т.п. (первые 32 непечатаемых знаков в 7-разрядном коде ASCII). Особенно полезна она, если нужно удалить перенос строки — мне всегда помогает.
Внимание! ПЕЧСИМВ способна удалить большинство, но не все лишние символы.
Как правильно выровнять в Excel текст в ячейке?
Специальная вставка в Excel
Как удалить текст или символ из ячейки в Excel
В статье рассматривается, как быстро удалить часть текста из ячеек Excel с помощью формул и встроенных функций.
В этом уроке мы рассмотрим наиболее распространенные случаи удаления символов в Excel. Хотите удалить определенный текст из нескольких ячеек? Или, может быть, удалить первый или последний символ в строке? Или, возможно, удалить только определенное вхождение данного символа? Какой бы ни была ваша задача, вы найдете для нее не одно решение!
- Как удалить определенные символы
- Удалить определенный символ из нескольких ячеек
- Удалить определенный символ из строки
- Удалить сразу несколько символов
- Как удалить часть текстовой строки
- Удалить текст из нескольких ячеек с помощью функции «Найти и заменить»
- Удалить часть текста из ячейки с формулой
- Удалить N-е вхождение определенного символа
- Удалить первый символ из строки
- Удалить последний символ из строки
- Обрезать пробелы до и после текста
- Специальные инструменты для удаления символов или текста в Excel
Как удалить определенный символ в Excel
Если вы хотите удалить определенный символ из ячеек Excel, есть два простых способа сделать это — инструмент «Найти и заменить» и формула.
Удаление символа из нескольких ячеек с помощью функции «Найти и заменить»
Принимая во внимание, что удаление символа — это не что иное, как замена его ничем, вы можете использовать функцию «Найти и заменить» Excel для выполнения этой задачи.
- Выберите диапазон ячеек, из которых вы хотите удалить определенный символ.
- Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить ».
- В поле Find what введите символ.
- Оставьте поле Замените на пустым.
- Щелкните Заменить все .
В качестве примера, вот как можно удалить символ # из ячеек с A2 по A6.
В результате символ решетки удаляется сразу из всех выделенных ячеек, а всплывающее диалоговое окно информирует вас о том, сколько замен было сделано:
Советы и примечания:
- Этот метод удаляет символы непосредственно из исходных данных. Если результат отличается от ожидаемого, нажмите Ctrl + Z, чтобы отменить изменение и вернуть исходные данные.
- Если вы имеете дело с буквенными символами, где регистр букв имеет значение, щелкните Параметры , чтобы развернуть диалоговое окно Найти и заменить , а затем установите флажок Учитывать регистр , чтобы выполнить поиск с учетом регистра .
Удалить определенный символ из строки с помощью формулы
Чтобы удалить определенный символ из любой позиции строки, используйте эту общую формулу ПОДСТАВКИ: , формула принимает следующий вид:
=ПОДСТАВИТЬ(A2, "#", "")
По сути, формула обрабатывает строку в A2 и заменяет каждый символ решетки (#) пустым нить ("").
Введите приведенную выше формулу в ячейку B2, скопируйте ее вниз через ячейку B6, и вы получите следующий результат:
Обратите внимание, что ПОДСТАВИТЬ всегда возвращает текстовую строку , даже если результат содержит только числа, как в ячейках B2 и B3. (обратите внимание на выравнивание по левому краю по умолчанию, типичное для текстовых значений).
Если вы хотите, чтобы результатом было число , оберните приведенную выше формулу в функцию ЗНАЧЕНИЕ следующим образом:
=ЗНАЧЕНИЕ(ПОДСТАВИТЬ(A2, "#", ""))
Или вы можете выполнить некоторую математическую операцию, которая не изменит исходное значение, например, добавить 0 или умножить на 1:
=ПОДСТАВИТЬ(A2, "#", "")*1
Удалить сразу несколько символов
Чтобы удалить несколько символов в одной формуле, просто вложите функции ПОДСТАВКИ одну в другую.
Например, чтобы избавиться от символа решетки (#), косой черты (/) и обратной косой черты (\), используйте следующую формулу:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2, "#"," "), "/", ""), "\", "")
Советы и примечания:
- Функция ПОДСТАВИТЬ чувствительна к регистру , имейте это в виду при работе с буквами.
- Если вы хотите, чтобы результаты были значениями , независимыми от исходных строк, используйте параметр Специальная вставка — значения , чтобы заменить формулы их значениями.
- В ситуации, когда необходимо удалить много разных символов, намного удобнее использовать пользовательскую функцию RemoveChars, определенную в LAMBDA.
Как удалить определенный текст из ячейки Excel
Два метода, которые мы использовали для удаления одного символа, одинаково хорошо обрабатывают последовательность символов.
Удалить текст из нескольких ячеек
Чтобы удалить определенный текст из каждой ячейки в выбранном диапазоне, нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить , а затем:
- Введите ненужный текст в поле Найти что Коробка .
- Оставить Заменить с пустым полем .
При нажатии кнопки Заменить все все замены будут выполнены за один раз:
Удаление определенного текста из ячейки с помощью формулы
Чтобы удалить часть текстовой строки, вы снова используете функцию ПОДСТАВИТЬ в ее основной форме:
ПОДСТАВИТЬ( ячейка , текст , "")
Например, чтобы удалить подстроку "mailto:" из ячейки A2, используйте следующую формулу:
= ПОДСТАВИТЬ(A2, "mailto:", "" )
Эта формула переходит в ячейку B2, а затем вы перетаскиваете ее вниз на столько строк, сколько необходимо:
Как удалить N-е вхождение определенного символа
В ситуации, когда вы хотите удалить определенное вхождение из конкретный символ, определите последний необязательный аргумент функции ПОДСТАВИТЬ.
В общей формуле ниже instance_num определяет, какой экземпляр указанного символа следует заменить пустой строкой:
Заместитель ( String , char , "", Encason_num )
Например:
, чтобы уничтожить 1 ST . /", "", 1)
Чтобы удалить 2-й -й -й символ косой черты, используйте следующую формулу:
=ПОДСТАВИТЬ(A2, "/", "", 2)
Как удалить первый символ
Чтобы удалить первый символ с левой стороны строки, вы можете использовать одну из следующих формул. Оба делают одно и то же, но по-разному.
ЗАМЕНИТЬ( ячейка , 1, 1, "")
В переводе на человеческий язык формула гласит: в указанной ячейке взять 1 символ ( num_chars ) с 1 й позиции (start_num) и замените его пустой строкой ("").
ПРАВО( ячейка , ДЛСТР( ячейка ) - 1)
Здесь мы вычитаем 1 символ из общей длины строки, которая вычисляется функцией ДЛСТР.
Разница передается RIGHT для извлечения этого количества символов с конца.
Например, чтобы удалить первый символ из A2, формулы выглядят следующим образом:
=ЗАМЕНИТЬ(A2, 1, 1, "")
=ПРАВО(A2, ДЛСТР(A2) - 1)
На снимке экрана ниже показана формула REPLACE. Формула RIGHT LEN даст точно такие же результаты.
Чтобы удалить любые n символов из начала строки, см. Как удалить символы слева в Excel.
Как удалить последний символ
Для удаления последнего символа из конца строки используется следующая формула:
ЛЕВЫЙ( ячейка , ДЛСТР( ячейка ) - 1)
Логика аналогична формуле ПРАВЫЙ ДЛСТР из предыдущего пример:
Вы вычитаете 1 из общей длины ячейки и передаете разницу функции ВЛЕВО, чтобы она могла извлечь столько символов из начала строки.
Например, вы можете удалить последний символ из A2, используя эту формулу:
=ЛЕВО(A2, ДЛСТР(A2) - 1)
Чтобы удалить любые n символов с конца строки, см.
Как удалить символы справа в Excel.
Удалить текст после определенного символа
Чтобы удалить все после данного символа, общая формула: просто: функция ПОИСК вычисляет позицию указанного символа и передает ее функции ВЛЕВО, которая возвращает соответствующее количество символов с самого начала. Чтобы не выводить сам разделитель, из результата ПОИСКА вычитаем 1.
Например, чтобы удалить текст после двоеточия (:), формула в B2 выглядит следующим образом:
=ЛЕВЫЙ(A2, ПОИСК(":", A2) -1)
Дополнительные примеры формул см. Удалить текст до или после определенного символа.
Как удалить пробелы до и после текста в Excel
В текстовых процессорах, таких как Microsoft Word, пробел перед текстом иногда добавляется намеренно, чтобы создать сбалансированный и элегантный поток для глаз читателя. В программах для работы с электронными таблицами начальные и конечные пробелы могут оставаться незамеченными и вызывать множество проблем.
К счастью, в Microsoft Excel есть специальная функция TRIM для удаления лишних пробелов.
Формула для удаления лишних пробелов из ячеек проста:
=ОБРЕЗАТЬ(A2)
Где A2 — исходная текстовая строка.
Как вы можете видеть на изображении ниже, он удаляет все пробелы перед текстом, после текста и между словами/подстроками, кроме одного символа пробела.
Если эта простая формула не работает для вас, то, скорее всего, на вашем листе есть неразрывные пробелы или непечатаемые символы.
Чтобы избавиться от них, конвертируйте неразрывные пробелы в обычные пробелы с помощью ЗАМЕНЫ:
ЗАМЕНА(A2, CHAR(160), " ")
Где 160 - кодовый номер символа неразрывного пробела ( ) .
Кроме того, используйте функцию CLEAN для удаления непечатаемых символов :
CLEAN(SUBSTITUTE(A2, CHAR(160), " "))
Вложите приведенную выше конструкцию в функцию TRIM, и вы Получим идеальную формулу для удаления пробелов до/после текста, а также неразрывных пробелов и непечатаемых символов:
=ОТРЕЗАТЬ(ОЧИСТИТЬ(ПОДСТАВИТЬ(A2, СИМВОЛ(160), " ")))
Дополнительные сведения см.
в разделе Как удалить пробелы в Excel.
Удаление символов в Excel с помощью мгновенного заполнения
В простых сценариях мгновенное заполнение Excel может оказать вам услугу и автоматически удалить символы или часть текста на основе предоставленного шаблона.
Допустим, у вас есть имя и адрес электронной почты в одной ячейке, разделенные запятой. Вы хотите удалить все после запятой (включая саму запятую). Чтобы это сделать, выполните следующие шаги:
- Вставьте пустой столбец справа от исходных данных.
- В первой ячейке вновь добавленного столбца введите значение, которое хотите сохранить (в нашем случае имя).
- Начните вводить значение в следующей ячейке. Как только Excel определит шаблон, он покажет предварительный просмотр данных, которые необходимо заполнить в ячейках ниже по тому же шаблону.
- Нажмите клавишу Enter, чтобы принять предварительный просмотр.
Готово!
Примечание. Если Excel не может распознать шаблон в ваших данных, заполните еще пару ячеек вручную, чтобы предоставить больше примеров.
Кроме того, убедитесь, что Flash Fill включен в вашем Excel. Если все равно не получится, то придется прибегнуть к какому-то другому методу.
Специальные инструменты для удаления символов или текста в Excel
В этом заключительном разделе представлены наши собственные решения для удаления текста из ячеек Excel. Если вам нравится находить простые способы решения сложных задач, вам понравятся удобные инструменты, включенные в Ultimate Suite.
На вкладке Ablebits Data в группе Текст есть три варианта удаления символов из ячеек Excel:
- Определенные символы и подстроки
- символов в определенной позиции
- Повторяющиеся символы
Чтобы удалить определенный символ или подстроку из выбранных ячеек, выполните следующие действия:
- Нажмите Удалить > Удалить символы .
- Выберите опцию, наиболее соответствующую вашим потребностям.
- Установите или снимите флажок С учетом регистра .
- Нажмите Удалить .
Ниже приведены несколько примеров, которые охватывают наиболее типичные сценарии.
Удалить определенный символ
Чтобы удалить определенные символы из нескольких ячеек одновременно, выберите Удалить пользовательские символы .
Например, мы удаляем все вхождения заглавных букв A и B из диапазона A2:A4:
Удалить предопределенный набор символов
Чтобы удалить определенный набор символов, выберите Удалить наборы символов и затем выберите один из следующих вариантов:
- Непечатаемые символы — удаляет любой из первых 32 символов в 7-битном наборе ASCII (кодовые значения от 0 до 31), включая символ табуляции, разрыв строки и т. д.
- Текстовые символы - удаляет текст и сохраняет цифры.
- Числовые символы - удаляет числа из буквенно-цифровых строк.
- Символы и знаки препинания — удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т.
д.
Удалить часть текста
Чтобы удалить часть строки, выберите параметр Удалить подстроку .
Например, чтобы извлечь имена пользователей из адресов Gmail, мы удаляем подстроку «@gmail.com»:
Вот как можно удалить текст и символы из ячеек Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Доступные загрузки
Удаление символов в Excel — примеры (файл .xlsm)
Ultimate Suite — ознакомительная версия (файл .exe)
Вас также может заинтересовать
Как удалить специальные/ненужные символы в Excel
В этой статье вы узнаете, как удалить определенные символы из текстовой строки и удалить ненужные символы из нескольких ячеек одновременно.
При импорте данных в Excel из другого места на ваши рабочие листы может попасть множество специальных символов. Что еще больше расстраивает, так это то, что некоторые символы невидимы, что создает дополнительные пробелы до, после или внутри текстовых строк.
Этот учебник предлагает решения для всех этих проблем, избавляя вас от необходимости просматривать данные ячейка за ячейкой и удалять ненужные символы вручную.
- Удалить специальный символ из строки
- Удалить несколько символов
- Удаляйте сразу все нежелательные символы с помощью LAMBDA
- Удаление специальных символов с помощью функций VBA
- Удалить непечатаемые символы
- Удаление специальных символов с помощью Ultimate Suite
Удалить специальный символ из ячейки Excel
Чтобы удалить определенный символ из ячейки, замените его пустой строкой с помощью функции ПОДСТАВИТЬ в самой простой форме:
ПОДСТАВИТЬ( ячейка , символ , "")
Например, чтобы удалить вопросительный знак из A2, формула в B2:
= ПОДСТАВИТЬ(A2, "?", "")
Чтобы удалить символ, которого нет на клавиатуре, вы можете скопировать/вставить его в формулу из исходной ячейки.
Например, вот как можно избавиться от перевернутого вопросительного знака:
=ЗАМЕНИТЬ(A2, "¿", "")
Но если нежелательным символом является невидимый или не правильно копирует, как в формулу вставить? Просто найдите его кодовый номер с помощью функции КОД.
В нашем случае нежелательный символ («¿») стоит последним в ячейке A2, поэтому мы используем комбинацию функций КОД и ПРАВИЛЬНО, чтобы получить его уникальное кодовое значение, которое равно 191:
=КОД(ПРАВЫЙ (A2))
Как только вы получите код персонажа, выполните соответствующую функцию CHAR для общей формулы выше. Для нашего набора данных формула выглядит следующим образом:
=ЗАМЕНИТЬ(A2, СИМВОЛ(191),"")
Примечание. Функция ПОДСТАВИТЬ чувствительна к регистру , что означает, что она обрабатывает строчные и прописные буквы как разные символы. Пожалуйста, имейте это в виду, если ваш нежелательный символ является буквой.
Удалить несколько символов из строки
В одной из предыдущих статей мы рассмотрели, как удалить определенные символы из строки в Excel путем вложения нескольких функций ПОДСТАВКИ одна в другую. Тот же подход можно использовать для удаления двух или более нежелательных символов за один раз:
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ячейка , char1 , "")), char2 , ""), char3 , "")
Например, чтобы удалить обычные восклицательные и вопросительные знаки инвертированные из текстовой строки в A2, используйте эту формулу:
= ПОДСТАВИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2, "!", ""), "¡", ""), "?", "" ), "¿", "")
То же самое можно сделать с помощью функции CHAR, где 161 - код символа для "¡" и 191 — код символа для "¿":
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3, "!", ""), "?", ""), CHAR(161), ""), CHAR (191), "")
Вложенные функции ПОДСТАВКИ прекрасно работают для разумного количества символов, но если вам нужно удалить десятки символов, формула становится слишком длинной и сложной для управления.
Следующий пример демонстрирует более компактное и элегантное решение.
Удалить сразу все ненужные символы
Решение работает только в Excel для Microsoft 365
Как вы, наверное, знаете, в Excel 365 есть специальная функция, позволяющая создавать собственные функции, включая те, которые выполняют рекурсивные вычисления. Эта новая функция называется LAMBDA, и вы можете найти полную информацию о ней в руководстве по ссылке выше. Ниже я проиллюстрирую эту концепцию парой практических примеров.
Пользовательская функция LAMBDA для удаления нежелательных символов выглядит следующим образом:
=LAMBDA(строка, символы, ЕСЛИ(символы<>"", RemoveChars(SUBSTITUTE(строка, ЛЕВЫЙ(символы, 1), "") , ПРАВО(символы, ДЛСТР(символы) -1)), строка))
Чтобы иметь возможность использовать эту функцию в своих рабочих листах, вам нужно сначала дать ей имя. Для этого нажмите Ctrl + F3, чтобы открыть Name Manager , а затем определите New Name следующим образом:
- В поле Name введите имя функции: RemoveChars .
- Установите область действия Workbook .
- В поле Относится к вставьте приведенную выше формулу.
- При желании введите описание параметров в поле Комментарии коробка. Параметры будут отображаться при вводе формулы в ячейку.
- Нажмите OK , чтобы сохранить новую функцию.
Подробные инструкции см. в разделе Как назвать пользовательскую функцию LAMBDA.
Как только функция получит имя, вы сможете обращаться к ней, как к любой родной формуле.
С точки зрения пользователя, синтаксис нашей пользовательской функции очень прост:
RemoveChars(string, chars)
Где:
- Строка - исходная строка или ссылка на ячейку/диапазон, содержащий строку(и).
- Символы - символы для удаления. Может быть представлен текстовой строкой или ссылкой на ячейку.
Для удобства вводим нежелательные символы в какую-нибудь ячейку, допустим D2.
Чтобы удалить эти символы из A2, используйте следующую формулу:
=RemoveChars(A2, $D$2)
Чтобы формула работала правильно, обратите внимание на следующее:
- В D2 символы перечислены без пробелов, если только вы не хотите удалять пробелы.
- Адрес ячейки, содержащей специальные символы, блокируется знаком $ ($D$2), чтобы предотвратить изменение ссылки при копировании формулы в ячейки ниже.
Затем мы просто перетаскиваем формулу вниз и удаляем все символы, перечисленные в D2, из ячеек с A2 по A6:
Чтобы очистить несколько ячеек с помощью одной формулы, укажите диапазон A2:A6 для 1-го аргумента:
=RemoveChars(A2:A6, D2)
Так как формула вводится только в самую верхнюю ячейку, вам не нужно беспокоиться о блокировке координат ячейки - относительная ссылка (D2) отлично работает в этом случае . А благодаря поддержке динамических массивов формула автоматически переносится во все указанные ячейки:
Удаление предопределенного набора символов
Чтобы удалить предопределенный набор символов из нескольких ячеек, вы можете создать другую лямбду, которая вызывает основную 9"))
Чтобы удалить числа из текстовых строк, мы создали еще одну функцию с именем RemoveNumbers :
=LAMBDA(string, RemoveChars(string, "0123456789"))
5 Обе функции 90 Приведенные выше функции очень просты в использовании, так как требуют только один аргумент - исходную строку
Чтобы удалить специальных символов из A2, используйте формулу:
=RemoveSpecialChars(A2)
0076 числовых символов:
=RemoveNumbers(A2)
Как работает эта функция:
В сущности, функция RemoveChars выполняет цикл по списку символов 4.
00004 и удаляет единицы по 90 символов по времени 03 по 90 символов. Перед каждым рекурсивным вызовом функция ЕСЛИ проверяет оставшиеся символы. Если строка chars не пуста (chars<>""), функция вызывает сама себя. Как только последний символ обработан, формула возвращает строка в своей текущей форме и завершает работу.
Подробное описание формулы см. в разделе Рекурсивная лямбда для удаления нежелательных символов.
Удаление специальных символов с помощью VBA
Функции работают во всех версиях Excel
Если функция LAMBDA отсутствует в вашем Excel, ничто не мешает вам создать аналогичную функцию с помощью VBA. Пользовательскую функцию (UDF) можно записать двумя способами.
Пользовательская функция для удаления специальных символов рекурсивный :
Этот код эмулирует логику функции LAMBDA, рассмотренной выше.
Функция RemoveUnwantedChars (str As String, chars As String) Если ("" <> символов) Тогда ул = заменить (ул, влево (символы, 1), "") chars = Right(chars, Len(chars) - 1) RemoveUnwantedChars = RemoveUnwantedChars(строка, символы) Еще RemoveUnwantedChars = ул.
Конец, если Конечная функция
Пользовательская функция для удаления специальных символов нерекурсивный :
Здесь мы циклически перебираем ненужные символы от 1 до Len(chars) и заменяем найденные в исходной строке ничем. Функция MID извлекает ненужные символы один за другим и передает их функции замены.
Функция RemoveUnwantedChars (str As String, chars As String) Для индекса = 1 To Len(символы) str = Заменить (str, Mid (символы, индекс, 1), "") Следующий RemoveUnwantedChars = ул. Конечная функция
Вставьте один из приведенных выше кодов в книгу, как описано в разделе Как вставить код VBA в Excel, и ваша пользовательская функция готова к использованию.
Чтобы не путать нашу новую пользовательскую функцию с функцией, определяемой Lambda, мы назвали ее по-другому:
RemoveUnwantedChars(string, chars)
Предполагая, что исходная строка находится в A2, а нежелательные символы в D2, мы можем избавьтесь от них, используя эту формулу:
= RemoveUnwantedChars(A2, $D$2)
Эта пользовательская функция называется RemoveSpecialChars и требует только один аргумент - исходную строку:
RemoveSpecialChars(string)
Чтобы удалить специальные символы из нашего набора данных, формула выглядит так:
=RemoveSpecialChars(A2)
Удалить непечатаемые символы в Excel
Microsoft Excel имеет специальную функцию для удаления непечатаемых символов - функцию CLEAN.
Технически, она удаляет первые 32 символа в 7-битном Набор ASCII (коды от 0 до 31)
Например, чтобы удалить непечатаемых символов из A2, используйте следующую формулу:
=CLEAN(A2)
Это удалит непечатаемые символы, но пробелы до/после текста и между словами останутся .
Чтобы избавиться от лишних пробелов , оберните формулу CLEAN в функцию TRIM: сводятся к одному пробелу:
Если вы хотите удалить абсолютно все пробелы внутри строки, то дополнительно замените символ пробела (кодовый номер 32) на пустую строку:
=TRIM(CLEAN((SUBSTITUTE(A2, CHAR( 32), ""))))
На вашем рабочем листе остались пробелы или другие невидимые символы? Это означает, что эти символы имеют разные значения в наборе символов Unicode.
Например, код символа неразрывного пробела ( ) равно 160, и вы можете очистить его, используя следующую формулу:
=ПОДСТАВИТЬ(A2, СИМВОЛ(160)," ")
сначала его кодовое значение.
Подробные инструкции и примеры формул находятся здесь: Как удалить конкретный непечатаемый символ.
Удаление специальных символов с помощью Ultimate Suite
Поддерживает Excel для Microsoft 365, Excel 2019–2010
В этом последнем примере позвольте мне показать вам самый простой способ удаления специальных символов в Excel. После установки Ultimate Suite вам нужно сделать следующее:
- На вкладке Ablebits Data в группе Text нажмите Удалить > Удалить символы .
- На панели надстройки выберите исходный диапазон, выберите Удалить наборы символов и выберите нужный параметр в раскрывающемся списке ( Символы и знаки препинания в этом примере).
- Нажмите кнопку Удалить .
Через мгновение вы получите отличный результат:
Если что-то пойдет не так, не беспокойтесь — резервная копия вашего рабочего листа будет создана автоматически, поскольку по умолчанию установлен флажок Создать резервную копию этого рабочего листа .
Видео-курс
