#017 Търсене и броене със Substitute

Функцията Substitute има за цел да подменя един текст с друг... Синтаксисът  и е =Substitute( текст в който се подменя, стария текст, новия текст, [коя появя на текстът да се подмени, ако се пропусне се подменят всички намерени текстове] )

Ето два примера:

• =SUBSTITUTE("0000001","0","*")  резултат: ******1
• =SUBSTITUTE("003592300838","00","+",1) Резултат: +3592300838 (замества със знака "+" само първите намерени две нули! (този пример е само за илюстрация и ако работите с мобилни номера ще се наложи по-сложни трикове!)

Функцията обаче се използва и в по-сложни формули по малко по-специален (и често труден за разбиране) начин.

Да се преброи колко пъти даден текст (или знак) го има в друг текст. Ето израза:
=Len(текстът в който търсим)-Len(Substitute(текстът в които търсим, текстът (знакът) който искаме да преброим, "") )
Трикът е, че се премахва търсения текст (подменя се с "") и се намира разликата между дължините на оригиналния и променения текст.

Пример 1: Да се преброят тиретата в даден текст.
Отговор: =LEN("123-345-678-987")-LEN(SUBSTITUTE("123-345-678-987","-",""))

Пример 2: Да се преброят интервалите в дадена клетка
Отговор: =LEN(A7)-LEN(SUBSTITUTE(A7," ","")) (Между първите кавички има интервал, а между вторите няма!)

Второ приложение е да се намери позицията на N-тото появяване на даден текст/знак.
=Find(char(7),Substitute(текст в който търсим, текст който търсим, Char(7), появяването което търсим)

Тук се прилага следния трик. Подменя се този текст/символ с нещо което знаем че го няма в текстът. Например  с Char(7) (камбанка):) Припомням за последния параметър на Substitute който позволява избирателна промяна. След което се намира позицията на този символ със Find.

Ето примери:
Пример 3: Да се намери позицията на ВТОРОТО тире в текст:
Отговор: =FIND(CHAR(7),SUBSTITUTE("123-345-678-987","-",CHAR(7),2))

Пример 4: Да се намери позицията на ТРЕТИЯ интервал в дадена клетка
Отговор: =FIND(CHAR(7),SUBSTITUTE(A7," ",CHAR(7),3))


И като десерт два сложни примера за тези които искат да си размърдат мозъците:)))

Пример 5: Да се намери броят на цифрите в дадена клетка. Не се броят точки и други символи. Само цифрите от нула до девет!
Отговор: =SUMPRODUCT(LEN(A8)-LEN(SUBSTITUTE(A8,ROW(INDIRECT("1:10"))-1,"")))

Тук се прави вътрешен цикъл за да се използва трикът с броенето. Единия вариант е в да се сложи масив {0,1,2,3...9} . Другия вариант е с Row (беше дискутиран вече в по-стари теми). Поради фактът че нулев ред няма (т.е. Row(0) ще даде грешка!) се брои от едно до десет и се вади единица и по този начин се получава броене от нула до девет! Задал съм в червено този "цикъл".

Пример 6. Да се извлече ПОСЛЕДНАТА дума от клетка. Думите са разделени с интервал. Не знаем колко думи има! Думите са повече от една (ако не сме сигурни, трябва да се направи проверка, която в отговора съм спестил!).

Отговор:
=MID(A11,FIND(CHAR(7),SUBSTITUTE(A11," ",CHAR(7), LEN(A11)-LEN(SUBSTITUTE(A11," ",""))))+1,32767)

Тук се използва функцията MID за извличане. За намирането на началната позиция се използва комбинация от двата метода за търсене и броене. Чрез броене се намира броя (т.е. колко интервала има) и след това се намира позицията на последния (=броя) интервал. Вместо да се прави сложна гимнастика за определяне броя на символите за извличане просто се посочва 32767 (текстът в една клетка не може да бъде повече от 32767!) и функцията Mid взема всичко до края. (За лимитите ето ви справка http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx )

Това е:)