Excel има набор от TEXT функции, които могат да направят чудеса. Можете да извършвате всякакви операции с нарязване и зарове на текст с помощта на тези функции.
Една от често срещаните задачи за хората, работещи с текстови данни, е да извлекат подниза в Excel (т.е. да получат psrt на текста от клетка).
За съжаление в Excel няма функция за подниза, която да може да направи това лесно. Това обаче може да се направи с помощта на текстови формули, както и някои други вградени функции на Excel.
Нека първо да разгледаме някои от текстовите функции, които ще използваме в този урок.
Функции на Excel TEXT
Excel има набор от текстови функции, които биха направили наистина лесно извличането на подниза от оригиналния текст в Excel. Ето функциите на Excel Text, които ще използваме в този урок:
- Функция RIGHT: Извлича посочения брой знаци отдясно на текстовия низ.
- Функция LEFT: Извлича посочения брой знаци отляво на текстовия низ.
- MID функция: Извлича посочения брой знаци от посочената начална позиция в текстов низ.
- FIND функция: Намира началната позиция на посочения текст в текстовия низ.
- LEN функция: Връща броя на знаците в текстовия низ.
Извличане на подниза в Excel с помощта на функции
Да предположим, че имате набор от данни, както е показано по -долу:
Това са някои случайни (но супергеройски) имейл идентификатори (с изключение на моя) и в примерите по-долу ще ви покажа как да извлечете потребителското име и името на домейна с помощта на текстовите функции в Excel.
Пример 1 - Извличане на потребителски имена от имейл идентификатори
Докато използвате текстови функции, е важно да идентифицирате модел (ако има такъв). Това улеснява изграждането на формула. В горния случай моделът е знакът @ между потребителското име и името на домейна и ние ще го използваме като препратка за получаване на потребителските имена.
Ето формулата за получаване на потребителското име:
= НАЛЯВО (A2, НАМЕРИ ("@", A2) -1)
Горната формула използва функцията LEFT за извличане на потребителското име чрез идентифициране на позицията на знака @ в идентификатора. Това става с помощта на функцията FIND, която връща позицията на @.
Например, в случай на [email protected], FIND (“@”, A2) ще върне 11, което е неговата позиция в текстовия низ.
Сега използваме функцията LEFT, за да извлечем 10 знака отляво на низа (един по -малък от стойността, върната от функцията LEFT).
Пример 2 - Извличане на име на домейн от имейл идентификатори
Същата логика, използвана в горния пример, може да се използва за получаване на името на домейна. Малка разлика тук е, че трябва да извлечем знаците отдясно на текстовия низ.
Ето формулата, която ще направи това:
= НАДЯСНО (A2, LEN (A2) -FIND ("@", A2))
В горната формула използваме същата логика, но я коригираме, за да сме сигурни, че получаваме правилния низ.
Нека отново вземем примера на [email protected]. Функцията FIND връща позицията на знака @, която в този случай е 11. Сега трябва да извлечем всички знаци след @. Така ние идентифицираме общата дължина на низ и изваждаме броя на знаците до @. Той ни дава броя знаци, които покриват името на домейна вдясно.
Сега можем просто да използваме функцията НАДЯСНО, за да получим името на домейна.
Пример 3 - Извличане на името на домейна от имейл идентификатори (без .com)
За да извлечете подниза от средата на текстов низ, трябва да идентифицирате позицията на маркера точно преди и след подниза.
Например, в примера по -долу, за да получите името на домейна без частта .com, маркерът ще бъде @ (което е точно преди името на домейна) и. (което е точно след него).
Ето формулата, която ще извлече само името на домейна:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Функцията Excel MID извлича определения брой знаци от определената начална позиция. В този пример по-горе, FIND (“@”, A2) +1 определя началната позиция (която е точно след@), а FIND (“.”, A2) -FIND (“@”, A2) -1 идентифицира брой знаци между „@„И„.‘
Актуализация: Един от читателите William19 спомена, че горната формула няма да работи, в случай че има точка (.) В имейл адреса (например [email protected]). Ето формулата за справяне с такива случаи:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Използване на текст в колони за извличане на подниза в Excel
Използването на функции за извличане на подниза в Excel има предимството да бъде динамично. Ако промените оригиналния текст, формулата автоматично ще актуализира резултатите.
Ако това може да не ви е необходимо, използването на функцията Текст в колони може да бъде бърз и лесен начин за разделяне на текста на поднизове въз основа на определени маркери.
Ето как да направите това:
- Изберете клетките, в които имате текст.
- Отидете на Данни -> Инструменти за данни -> Текст в колони.
- В Съветника за текст в колона Стъпка 1, изберете Разграничено и натиснете Напред.
- В Стъпка 2, проверете опцията Други и въведете @ в полето точно до нея. Това ще бъде нашият разделител, който Excel ще използва за разделяне на текста на поднизове. Можете да видите визуализацията на данните по -долу. Щракнете върху Напред.
- В Стъпка 3 Общите настройки работят добре в този случай. Можете обаче да изберете различен формат, ако разделяте числа/дати. По подразбиране клетката дестинация е мястото, където имате оригиналните данни. Ако искате да запазите оригиналните данни непокътнати, променете това в друга клетка.
- Щракнете върху Край.
Това незабавно ще ви даде два набора поднизове за всеки имейл имейл, използван в този пример.
Ако искате допълнително да разделите текста (например, разделете batman.com на batman и com), повторете същия процес с него.
Използване на FIND и REPLACE за извличане на текст от клетка в Excel
FIND и REPLACE могат да бъдат мощна техника, когато работите с текст в Excel. В примерите по -долу ще научите как да използвате FIND и REPLACE с заместващи знаци, за да правите невероятни неща в Excel.
Вижте също: Научете всичко за заместващите знаци в Excel.
Да вземем същите примери за идентификатори на имейли.
Пример 1 - Извличане на потребителски имена от имейл идентификатори
Ето стъпките за извличане на потребителски имена от имейл идентификатори с помощта на функцията „Намери и замени“:
- Копирайте и поставете оригиналните данни. Тъй като Find and Replace работи и променя данните, върху които се прилага, най -добре е да имате резервно копие на оригиналните данни.
- Изберете данните и отидете на Начало -> Редактиране -> Намиране и избор -> Замяна (или използвайте клавишната комбинация Ctrl + H).
- В диалоговия прозорец Намиране и подмяна въведете следното:
- Намери какво: @*
- Заменете с: (оставете това поле празно)
- Кликнете върху Замени всички.
Това незабавно ще премахне целия текст преди @ в имейл идентификаторите. Ще получите резултата, както е показано по -долу:
Как работи това ?? - В горния пример сме използвали комбинация от @ и *. Звездичка (*) е заместващ знак, който представлява произволен брой знаци. Следователно, @* ще означава текстов низ, който започва с @ и може да има произволен брой знаци след него. Например в [email protected], @* ще бъде @batman.com. Когато заменим @* с празно, той премахва всички знаци след @(включително @).
Пример 2 - Извличане на име на домейн от имейл идентификатори
Използвайки същата логика, можете да промените критериите „Намерете какво“, за да получите името на домейна.
Ето стъпките:
- Изберете данните.
- Отидете на Начало -> Редактиране -> Намиране и избор -> Замяна (или използвайте клавишната комбинация Ctrl + H).
- В диалоговия прозорец Намиране и замяна въведете следното:
- Намери какво: *@
- Заменете с: (оставете това поле празно)
- Кликнете върху Замени всички.
Това незабавно ще премахне целия текст преди @ в имейл идентификаторите. Ще получите резултата, както е показано по -долу:
