Как найти специальные символы в строку в Excel
Мы знаем, что link: / excel-text-formulas-excel-trim-function [TRIM]
и link: / excel-text-formulas-how-to-use-the-clean-function-in-excel [CLEAN]
Функции Excel используются для удаления непечатаемых символов и лишних пробелов из строк, но они не очень помогают при идентификации строк, содержащих какие-либо специальные символы, такие как @ или! и т.д. В таких случаях мы используем UDF.
Поэтому, если вы хотите узнать, содержит ли строка какие-либо специальные символы, вы не найдете формулы или функции Excel для этого. Мы можем найти специальные символы на клавиатуре, набрав их вручную, но вы не можете узнать, есть ли символы в строке или нет.
Поиск любых специальных символов может быть очень важным для очистки данных. А в некоторых случаях это необходимо сделать. Так как же это сделать в Excel? Как мы можем узнать, содержит ли строка какие-либо специальные символы?
Что ж, для этого мы можем использовать UDF. Приведенная ниже формула вернет ИСТИНА, если какая-либо ячейка содержит символы, отличные от 1 до 0 и от A до Z (в обоих случаях). Если он не найдет никаких специальных символов, он вернет FALSE.
Общая формула
|
=ContainsSpecialCharacters(string) |
Строка: строка, которую вы хотите проверить на наличие специальных символов.
Чтобы эта формула работала, вам нужно будет поместить приведенный ниже код в модуль вашей книги.
Итак, откройте Excel. Нажмите ALT + F11, чтобы открыть VBE. Вставьте модуль из меню «Вставка». Скопируйте приведенный ниже код и вставьте его в модуль.
Function ContainsSpecialCharacters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select Next End Function
Теперь функция готова к использованию.
Перейдите к листу в книге, содержащему строки, которые вы хотите проверить.
Запишите приведенную ниже формулу в ячейку C2:
|
=ContainsSpecialCharacters(B13) |
Он возвращает ИСТИНА для первой строки, поскольку она содержит специальный символ. Когда вы копируете формулу вниз, она показывает ЛОЖЬ для строки B14 и так далее. Но, как ни странно, для последней строки «Exceltip.com» отображается ИСТИНА. Это потому, что он содержит точку (.). Но почему так? Давайте изучим код, чтобы понять.
Как работает функция?
Мы перебираем все символы строки, используя link: / vba-for-loops-with-7-examples [For loop]
и средняя функция VBA. Функция Mid извлекает по одному символу из строки и сохраняет его в переменной ch.
For I = 1 To Len(str) ch = Mid(str, I, 1)
Теперь идет основная часть. Мы используем оператор select case, чтобы проверить, что содержит переменная ch.
Мы говорим VBA проверить, содержит ли ch какое-либо из определенных значений. Я определил от 0 до 9, от A до Z, от a до z и «» (пробел). Если c h содержит любой из них, мы устанавливаем его значение в False.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False
Вы можете видеть, что у нас нет точки (.) В списке, и именно поэтому мы получаем ИСТИНА для строки, содержащей точку. Вы можете добавить в список любой символ, который будет исключен из формулы.
Если ch содержит какой-либо символ, кроме перечисленных, мы устанавливаем результат функции на True и заканчиваем цикл прямо здесь.
Case Else ContainsSpecialCharacters = True Exit For
Так что да, вот как это работает. Но если вы хотите очистить специальные символы, вам нужно будет внести некоторые изменения в функцию.
Как удалить специальные символы из строк в Excel? Чтобы удалить специальные символы из строки в Excel, я создал еще одну настраиваемую функцию в VBA. Синтаксис функции:
|
=CleanSpecialCharacters(string) |
Эта функция возвращает очищенную версию переданной в нее строки.
Новая строка не будет содержать никаких специальных символов. Код этой функции выглядит так:
Function CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'Do nothing Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Скопируйте это в тот же модуль, который вы скопировали в приведенном выше коде.
Когда вы используете эту формулу для строк, результат будет следующим:
Вы можете видеть, что из строки удален каждый специальный символ, включая точку.
Как это работает?
Работает так же, как и вышеуказанная функция. Единственная разница в том, что эта функция заменяет каждый специальный символ нулевым символом. Формирует новую строку и возвращает эту строку.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Если вы хотите освободить какой-либо персонаж от очистки, вы можете добавить его в список в коде. Excel простит вам этот особый символ.
Так что да, ребята, вот как вы можете найти и заменить специальные символы из строки в Excel. Надеюсь, это было достаточно объяснительным и полезным.
Если это не поможет вам в решении вашей проблемы, сообщите нам об этом в разделе комментариев ниже.
Статьи по теме:
link: / excel-text-formulas-excel-trim-function [Как использовать функцию TRIM в Excel]: * Функция TRIM используется для обрезки строк и очистки любых конечных или ведущих пробелов из строки. Это очень помогает нам в процессе очистки данных.
link: / excel-text-formulas-how-to-use-the-clean-function-in-excel [Как использовать функцию CLEAN в Excel]: функция очистки используется для удаления непечатаемых символов из строки. Эта функция в основном используется с функцией TRIM для очистки импортированных посторонних данных.
link: / excel-text-replace-text-from-end-of-a-string-from-variable-position [Заменить текст с конца строки, начиная с позиции переменной]: * Чтобы заменить текст с конца строку, мы используем функцию REPLACE.
Функция REPLACE использует позицию текста в строке для его замены.
link: / excel-text-formulas-check-a-list-of-text-in-string [Как проверить, содержит ли строка один из многих текстов в Excel]: * Чтобы найти, проверьте, содержит ли строка какой-либо из несколько текстов, мы используем эту формулу. Мы используем функцию SUM, чтобы суммировать все совпадения, а затем выполняем логику, чтобы проверить, содержит ли строка какую-либо из нескольких строк.
link: / counting-count-cells-that-contain-specific-text [Подсчет ячеек, содержащих определенный текст]: * Простая функция СЧЁТЕСЛИ сотворит чудеса. Чтобы подсчитать количество нескольких ячеек, содержащих заданную строку, мы используем оператор подстановочного знака с функцией СЧЁТЕСЛИ.
link: / excel-text-formulas-excel-replace-vs-substitute-function [Excel REPLACE vs SUBSTITUTE]: * Функции REPLACE и SUBSTITUTE часто понимаются неправильно. Чтобы найти и заменить заданный текст, мы используем функцию ЗАМЕНА. Где REPLACE используется для замены ряда символов в строке…
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-вашу-продуктивность [50 комбинаций клавиш Excel для повышения вашей продуктивности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию Excel VLOOKUP] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов. link: / tips-countif-in-microsoft-excel [Как использовать]
link: / введение-формулы-и-функции-функции-vlookup [Excel]
link: / tips-countif-in-microsoft-excel [Функция СЧЁТЕСЛИ] | Подсчитайте значения с условиями, используя эту удивительную функцию.
Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.