СУБСТИТУТ Функция Excel - Намерете и заменете текст

Изтеглете Примерна работна книга

Изтеглете примерната работна книга

Този урок демонстрира как да използвате Функция за заместване на Excel в Excel, за да намерите и замените текст.

ЗАМЕСТНИК Общ преглед на функцията

Функцията SUBSTITUTE Намира и заменя съществуващия текст с нов текстов низ. Различаващ главни от малки букви.

За да използвате функцията SUBSTITUTE Excel Worksheet, изберете клетка и въведете:

(Забележете как се появяват входовете на формулата)

Функция SUBSTITUTE Синтаксис и входове:

1	= SUBSTITUTE (текст, стар_текст, нов_текст, брой_инстанция)

текст - Оригиналният низ от текст.

old_text - Текстовият низ, който искате да намерите и замените.

new_test - Текстът за подмяна.

instance_num - ОПТИОНАЛЕН. Номерът на екземпляра за замяна. Ако е празно, всички екземпляри се заменят.

Какво е SUBSTITUTE?

Функцията SUBSTITUTE в Excel се използва за заместване на низ от знаци в текстов низ с различен посочен текстов низ.

Нека разгледаме следния пример, заместващ низа „бързо кафяво“ с „бавно бяло“.

1	= ЗАМЕСТНИК (C2, C3, C4)

SUBSTITUTE гледа в низа в C2, намира „бързо кафяво“ и го заменя с „бавно бяло“.

Как да използвате SUBSTITUTE

Функцията SUBSTITUTE приема 3 задължителни аргумента и 1 незадължителен:

• Текст: Текстът, който искате да замените знаци
• Old_text: Текстът, който искате да замените
• New_text: Текстът, с който искате да замените Old_text
• Instance_num (незадължително): Указва появата на Old_text, който искате да замените с New_text. По подразбиране всички случаи на Old_text ще бъдат заменени, ако не е посочен екземпляр.

Вложен гнездо SUBSTITUTE

Ами ако трябваше да заменим две части от един и същ низ? Нека да видим как бихме комбинирали две функции SUBSTITUTE.

1	= ЗАМЕСТИТЕЛ (ЗАМЕСТНИК (C2, C3, C4), C5, C6)

Забележете как резултатът от SUBSTITUTE (C2, C3, C4) се използва като първи аргумент за втория SUBSTITUTE. Ако разделим формулата на части:

= SUBSTITUTE (C2, C3, C4) се оценява на „The бавно бяло лисица прескача мързеливото куче ”.

След това SUBSTITUTE („Бавната бяла лисица прескача мързеливото куче“, C5, C6), която се оценява на „Бавното бяло лъв прескача мързеливото куче ”.

Разлика между SUBSTITUTE и REPLACE

Подобна на SUBSTITUTE функция е REPLACE. В повечето случаи те могат да се използват взаимозаменяемо, но се прилага следното стандартно правило.

Функцията SUBSTITUTE трябва да се използва, когато низ от текст да бъде заменен е известен, или цяла дума или думи се заменят, като нашия пример за лисица, използван по -рано.

1	= ЗАМЕСТНИК (C2, C3, C4)

Функцията REPLACE трябва да се използва, когато позицията на текстовите знаци да бъдат заменени в низа познати, или когато част от дума или низ се заменя. Например премахване на тире от низ от числа.

Функцията REPLACE също може да бъде вложена като SUBSTITUTE, но е важно да се отбележи, че относителната позиция на началния знак може да се промени в зависимост от дължината на низ, който трябва да бъде заменен в първата стъпка на вложената формула.

Например, замяната на „бързо кафяво“ с „бавно бяло“ означава замяна на 11 знака с 10 знака. Следователно, ако е вложен, началният знак за втората REPLACE ще бъде с 1 знак разстояние от първоначалните позиции на знаци.

Съвети за заместителя

• SUBSTITUTE може да се използва за поднизове:
• Не забравяйте, че трябва да посочите instance_num, ако се опитвате да замените набор от знаци, които се повтарят в низа:

Забележете, че 3030 е неправилен, тъй като „20“ и „20“ в „2020“ бяха заменени с „30“.

Интересувате ли се от още функции за текст?

Вижте другите ни статии за повече информация относно REPLACE или как се използват други текстови функции в Excel, като LEFT, MID и RIGHT.

SUBSTITUTE в Google Таблици

Функцията SUBSTITUTE работи абсолютно същото в Google Sheets като в Excel:

допълнителни бележки

Функцията SUBSTITUTE е чувствителна към регистъра !. Можете да използвате долните или горните функции, за да преобразувате вашите низове текст в последователен регистър, преди да използвате функцията SUBSTITUTE. След това, ако желаете, можете да използвате ПРАВИЛНАТА функция за главни букви на всяка дума.

Като алтернатива можете да използвате функциите SEARCH и REPLACE, за да симулирате независим от регистъра SUBSTITUTE. Използвайте функцията SEARCH, за да намерите началната позиция на текстовия низ. Функцията SEARCH не е чувствителна към регистъра. След това използвайте функцията REPLACE, за да замените текста. Ще трябва да използвате резултата от функцията SEARCH като вход за начален_ номер в REPLACE. Също така ще трябва да определите броя на знаците, които да замените (num_chars). Можете да ги преброите ръчно или да използвате функцията LEN, за да преброите броя знаци. Пример:

Заместване - Премахване на тирета

Често срещан въпрос е как да премахнете тирета от текста. Номерът е да замените тирето с нищо.

За този пример, нека приемем, че клетка A1 има текст с тирета. Ето как да ги премахнете

1. В празна помощна клетка тип = SUBSTITUTE (
2. Щракнете върху клетката, в която искате да замените знаците, или A1 (това автоматично попълва формулата ви)
3. Въведете, (запетая)
4. Въведете „-“
5. Въведете, (запетая)
6. Въведете “” и натиснете enter
Крайният резултат във вашата помощна клетка трябва да изглежда така:
= ЗАМЕСТИТЕЛ (A1, ”-“, ””)

3 Още примери за заместване:

Разглеждайки клетка В3, искаме да заменим думата „болен“ с „голям“. Можем да използваме следния израз:

1	= ЗАМЕСТНИК (B3, "болен", "голям", 1)

Ако текстът, който искаме да заменим, се появява повече от веднъж, тогава трябва да посочим инстанцията, която имаме предвид. Във втория пример имаме две появления на думата „стар“. Така че, ако искаме да променим втората инстанция на думата „сиво“, тогава трябва да използваме:

1	= ЗАМЕСТНИК (B4, "стар", "сив", 2)

Обърнете внимание, че ако събитието не е посочено, всички екземпляри се заменят. Така:

1	= ЗАМЕСТИТЕЛ (B4, "стар", "сив")

Ще се отървем от всички случаи на думата „стар“ и ще ги заменим с думата „сиво“. Имайте предвид, че ако старият текст не може да бъде намерен, тогава низът е непроменен. И така, гледайки последния пример:

1	= ЗАМЕСТНИК (B5, "черен", "сив", 1)

Означава, че се опитваме да заменим думата „черен“ с думата „сив“. Въпреки това, тъй като „черно“ не се появява, оригиналният низ остава левитра, изпратен в Съединените щати непроменен:

За да изтеглите .XLSX файла от тази статия, щракнете тук

ЗАМЕСТИ Примери във VBA

Можете също да използвате функцията SUBSTITUTE във VBA. Тип:
application.worksheetfunction.substitute (текст, стар_текст, нов_текст, номер на екземпляр)
За аргументите на функцията (текст и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо това.

Когато се изпълни следният код

1	Диапазон ("A1") = Работен лист Функция. Заместване (Обхват ("A1"), "използване", "", 1)

Съдържанието на клетка А1 ще стане:

И така, думата „използване“ беше заменена с празен низ. Последният параметър (1) показва кой екземпляр от текста трябва да бъде заменен, в случай че текстът присъства повече от един път. Така например, ако изпълним следното изявление

1	Range ("A1") = WorksheetFunction.Substitute (Range ("A1"), "the", "an", 1)

А1 клетката ще изглежда така

Забележете, че първото появяване на „the“ е заменено с „an“, докато второто „the“ не е засегнато. Ако пропуснахме последния параметър, и двете думи „the“ щяха да бъдат заменени

Връщане към списъка с всички функции в Excel