Функцията 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 )
Това е:)
