Všeobecný vzorec
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-50),99))
Zhrnutie
Ak chcete extrahovať slovo, ktoré obsahuje konkrétny text, môžete použiť vzorec založený na niekoľkých funkciách, napríklad TRIM, LEFT, SUBSTITUTE, MID, MAX a REPT. Tento vzorec môžete použiť na extrahovanie vecí, ako sú e-mailové adresy alebo iné podreťazce, s jedinečným ID.
V zobrazenom príklade je vzorec v C5:
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(B5," ",REPT(" ",99)))-50),99))
Vysvetlenie
Zhrnutie: tento vzorec „zaplavuje“ priestor medzi slovami v textovom reťazci s veľkým počtom medzier, vyhľadáva a extrahuje podreťazec záujmu a na vyčistenie neporiadku používa funkciu TRIM.
Pri práci zvnútra von je pôvodný text v B5 zaplavený medzerami pomocou funkcie SUBSTITUTE:
SUBSTITUTE(B5," ",REPT(" ",99))
Toto nahradí každý jeden priestor 99 medzerami.
Poznámka: 99 je iba ľubovoľné číslo, ktoré predstavuje najdlhšie slovo, ktoré potrebujete extrahovať.
Ďalej funkcia FIND nájde konkrétny znak (v tomto prípade „@“) vo vnútri zaplaveného textu:
FIND("@",SUBSTITUTE(B5," ",REPT(" ",99)))-50
FIND vráti v tomto texte pozíciu „@“, od ktorej sa odpočíta 50. Odčítanie hodnoty 50 účinne „vráti dozadu“ pozíciu kamkoľvek do stredu medzery predchádzajúcej záujmový podreťazec. V zobrazenom príklade je vypočítaná pozícia 366.
Funkcia MAX sa používa na riešenie problému podreťazca, ktorý sa v texte objaví ako prvý. V takom prípade bude pozícia záporná a na resetovanie hodnoty 1 sa použije MAX.
S vytvorenou počiatočnou pozíciou sa MID používa na extrakciu 99 znakov textu, počnúc 366 z textu v B5, opäť zaplaveného medzerou:
MID(SUBSTITUTE(B5," ",REPT(" ",99)),366,99)
Toto extrahuje podreťazec záujmu, s mnohými medzerníkmi pred a po.
Nakoniec sa funkcia TRIM použije na orezanie úvodného a koncového priestoru a vráti podreťazec obsahujúci špeciálny znak.
