Знаем, че функциите TRIM и CLEAN Excel се използват за почистване на непечатващи се символи и допълнителни интервали от низове, но те не помагат много при идентифицирането на низове, съдържащи специални символи като @ или! и т.н. В такива случаи използваме UDF.
Така че, ако искате да знаете дали низ съдържа някакви специални знаци, няма да намерите формула или функция на Excel за това. Можем да намерим специалните символи, които са на клавиатурата, като въведете ръчно, но не можете да знаете дали има символи в низа или не.
Намирането на специални символи може да бъде много важно за целите на почистване на данни. И в някои случаи трябва да се направи. И така, как да направим това в Excel? Как можем да разберем дали низ съдържа някакви специални знаци? Е, можем да използваме UDF за това.
Формулата по -долу ще върне TRUE, ако някоя клетка съдържа някакви знаци, различни от 1 до 0 и A до Z (и в двата случая). Ако не намери специални символи, той ще върне FALSE.
Обща формула
| = СъдържаSpecialCharacters (низ) |
Низ: Низът, който искате да проверите за специални знаци.
За да работи тази формула, ще трябва да поставите кода по -долу в модула на вашата работна книга.
Така че отворете Excel. Натиснете ALT+F11, за да отворите VBE. Поставете модул от менюто Вмъкване. Копирайте кода по -долу и го поставете в модула.
Функция СъдържаSpecialCharacters (str As String) As Boolean For I = 1 To Len (str) ch = Mid (str, I, 1) Изберете регистър ch Дело "0" До "9", "A" До "Z", "a "To" z "," "ContainsSpecialCharacters = False Case Друг ContainsSpecialCharacters = True Изход за край Изберете следваща крайна функция
Сега функцията е готова за използване.
Отидете на работния лист в работната книга, който съдържа низовете, които искате да проверите.
Напишете следната формула в клетка C2:
| = СъдържаSpecialCharacters (B13) |
Той връща TRUE за първия низ, тъй като съдържа специален знак. Когато копирате формулата надолу, тя показва FALSE за низ B14 и така нататък.
Но странно показва TRUE за последния низ "Exceltip.com". Това е така, защото съдържа точка (.). Но защо така? Нека разгледаме кода, за да разберем.
Как функционира функцията?
Ние правим итерация през всички символи на низа, като използваме цикъла For и mid функцията на VBA. Функцията Mid извлича един символ наведнъж от низ и го съхранява в променлива ch.
За I = 1 Към Len (str) ch = Mid (str, I, 1)
Сега идва основната част. Използваме оператора select case, за да проверим какво съдържа променливата ch.
Казваме на VBA да провери дали ch съдържа някоя от дефинираните стойности. Определих 0 до 9, от A до Z, от a до z и "" (интервал). Ако c
h съдържа някое от тези, задаваме стойността му на False.
Изберете Case ch Дело "0" До "9", "A" До "Z", "a" До "z", "ContainsSpecialCharacters = False
Можете да видите, че нямаме точка (.) В списъка и затова получаваме TRUE за низ, който съдържа точка. Можете да добавите всеки знак към списъка, за да бъдете освободени от формулата.
Ако ch съдържа някакъв знак, различен от изброените символи, ние задаваме резултата на функцията на True и приключваме цикъла точно там.
Дела в други случаи съдържаSpecialCharacters = Истински изход за
Така че да, така работи. Но ако искате да изчистите специални символи, ще трябва да направите някои промени във функцията.
Как да почистите специални знаци от низове в Excel?
За да изчистя специални символи от низ в Excel, създадох друга персонализирана функция във VBA. Синтаксисът на функцията е:
| = CleanSpecialCharacters (низ) |
Тази функция връща изчистена версия на низа, предаден в нея. Новият низ няма да съдържа никакви специални знаци.
Кодът на тази функция изглежда така:
Функция CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Изберете Case ch Case "0" To "9", "A" To " Z "," a "Към" z "," "'Не правете нищо Друг случай nstr = Замяна (nstr, ch," ") Край Изберете Следващ CleanSpecialCharacters = nstr Крайна функция
Копирайте това в същия модул, който сте копирали в горния код.
Когато използвате тази формула за низовете, резултатите ще бъдат следните:
Можете да видите, че всеки специален знак е премахнат от низ, включително точката.
Как работи?
Работи по същия начин като горната функция. Единствената разлика е, че тази функция замества всеки специален знак с нулев знак. Формира нов низ и връща този низ.
Изберете Case ch Дело "0" До "9", "A" To "Z", "a" To "z", "" ContainsSpecialCharacters = False Case Други nstr = Замяна (nstr, ch, "") Край Изберете
Ако искате да освободите всеки знак от почистване, можете да го добавите към списъка в кода. Excel ще прости този специален знак.
Така че да, момчета, ето как можете да намерите и замените специални символи от низ в Excel. Надявам се, че това беше достатъчно обяснително и полезно. Ако това не ви помогне при решаването на проблема, уведомете ни в секцията за коментари по -долу.
Как да използвате функцията TRIM в Excel: Функцията TRIM се използва за подрязване на низове и почистване на всички крайни или водещи пространства от низ. Това ни помага много в процеса на почистване на данни.
Как да използвате функцията CLEAN в Excel: Функцията Clean се използва за почистване на непечатващи се символи от низ. Тази функция се използва най -вече с функцията TRIM за почистване на внесени чужди данни.
Заменете текст от края на низ, започвайки от променлива позиция: За да заменим текст от края на низ, използваме функцията REPLACE. Функцията REPLACE използва позицията на текста в низ, за да го замени.
Как да проверите дали низ съдържа един от многото текстове в Excel: За да проверим дали низ съдържа някой от множество текст, използваме тази формула. Използваме функцията SUM, за да обобщим всички съвпадения и след това изпълняваме логика, за да проверим дали низът съдържа някой от множеството низове.
Преброяване на клетки, които съдържат определен текст: Една проста функция COUNTIF ще направи магията. За да преброим броя на множество клетки, които съдържат даден низ, използваме заместващия оператор с функцията COUNTIF.
Excel REPLACE vs SUBSTITUTE функция: Функциите REPLACE и SUBSTITUTE са най -неразбраните функции. За да намерим и заменим даден текст, използваме функцията SUBSTITUTE. Когато REPLACE се използва за замяна на редица знаци в низ …
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.