Обща формула
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Обобщение
За да премахнете част от естествената сложност на текста (препинателни знаци, нормализиране на малки и малки букви, премахване на излишни интервали), можете да използвате формула, базирана на функцията ЗАМЕНИТЕЛ, с помощта на функциите TRIM и LOWER.
Контекст
Може да има моменти, когато трябва да премахнете част от променливостта на текста преди друга обработка. Един пример е, когато искате да преброите конкретни думи в по-големи текстови низове. Тъй като Excel не осигурява поддръжка за регулярни изрази, не можете да създавате точни съвпадения. Например, ако искате да преброите колко пъти думата „лисица“ се появява в клетка, в крайна сметка ще преброите „лисици“. Можете да потърсите „лисица“ (с интервал), но това ще се провали с „лисица“ или „лисица“. Едно заобиколно решение е да се опрости текстът първо с формула в помощна колона, а след това да се брои на опростената версия. Примерът на тази страница показва един начин за това.
Обяснение
Формулата, показана в този пример, използва поредица от вложени функции ЗАМЕСТИТЕЛ за премахване на скоби, тирета, двоеточия, точки и запетая, удивителен знак, запетаи и точки. Процесът се изпълнява отвътре навън, като всеки ЗАМЕСТИТЕЛ замества един символ с едно интервал, след което се предава на следващия ЗАМЕСТИТЕЛ. Най-вътрешният СУБСТИТУТ премахва левите скоби и резултатът се предава на следващия ЗАМЕСТИТЕЛ, който премахва десните скоби и т.н.
Във версията по-долу са добавени прекъсвания на редове за четливост и за по-лесно редактиране на замените. Excel не се интересува от прекъсвания на редове във формули, така че можете да използвате формулата такава, каквато е.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
След като всички замествания приключат, резултатът се изпълнява чрез TRIM, за да се нормализират интервалите, след това функцията LOWER, за да принуди целия текст с малки букви.
Забележка: Ще трябва да коригирате действителните заместители, за да отговарят на вашите данни.
Добавяне на водещо и последващо пространство
В някои случаи може да искате да добавите интервал в началото и края на изчистения текст. Например, ако искате да преброите точно думи, може да потърсите думата, заобиколена от интервали (т.е. да търсите „лисица“, „карта“), за да избегнете фалшиви съвпадения. За да добавите водещо и последващо пространство, просто свържете интервал ("") към началото и края:
=" "&formula&" "
Където "формула" е по-дългата формула по-горе.
