Všeobecný vzorec
=MID(A1,1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
Zhrnutie
Na odstránenie posledného slova z textového reťazca môžete použiť vzorec založený na funkcii MID s pomocou SUBSTITUTE, LEN a FIND. V zobrazenom príklade je vzorec v bunke B5:
=MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)
Vysvetlenie
Tento vzorec využíva funkciu MID na odstránenie posledného slova z textového reťazca. Hlavnou výzvou je prísť na to, kde sa začína posledné slovo.
Vzorec je trochu komplikovaný, ale kroky sú jednoduché. Najskôr spočítame, koľko medzier v texte existuje, pomocou znakov LEN a SUBSTITUTE. Ďalej použijeme trochu nejasný argument „inštancia“ vo funkcii SUBSTITUTE, aby sme posledný priestor nahradili vlnovkou (~). Nakoniec pomocou FIND zistíme, kde je tilda, a funkciou MID vyradíme všetko, čo je po tilde.
Pri práci zvnútra narážame pomocou funkcií LEN a SUBSTITUTE na to, koľko medzier sa objaví v nasledujúcom:
LEN(B5)-LEN(SUBSTITUTE(B5," ","")) // returns 6
Pre text v B5 sa vráti 6. Táto stránka podrobnejšie vysvetľuje túto časť vzorca. Toto nám hovorí, že chceme text vystrihnúť na znak šiestej medzery.
Poznámka: Ak sú slová oddelené viac ako jednou medzerou, budete najskôr chcieť normalizovať medzery pomocou funkcie TRIM.
Číslo 6 sa potom pripojí k inému SUBSTITUTE ako „číslo inštancie“:
SUBSTITUTE(B5," ","~",6) // insert tilde
Tu nahradíme šiestu inštanciu medzery („“) vlnovkou (~). Po spustení SUBSTITUTE vráti tento reťazec:
"It's been seven hours and fifteen~days"
Poznámka: Tildu (~) používame iba preto, že sa jedná o zriedka sa vyskytujúci znak. Môžete použiť ľubovoľný znak, ktorý sa vám páči, pokiaľ sa nenachádza v zdrojovom texte.
Ďalej pomocou funkcie FIND nájdeme vlnovku:
FIND("~","It's been seven hours and fifteen~days")-1
FIND vráti hodnotu 34, pretože tilda je 34. znakom. Od tohto čísla odčítame 1, pretože do konečného výsledku nechceme zahrnúť poslednú medzeru. Teraz môžeme vzorec zjednodušiť na:
=MID(B5,1,33) // extract final text
Funkcia MID potom vráti znaky 1-33:
"It's been seven hours and fifteen"
S vlastným oddeľovačom
Rovnaký vzorec možno použiť s iným oddeľovačom. Napríklad na odstránenie všetkého textu za poslednou lomkou „/“ môžete použiť:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)
Odstrániť posledných n slov
Vzorec môžete upraviť tak, aby ste odstránili posledné 2 slová, posledné 3 slová atď. Všeobecná forma je:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,d,"~",LEN(A1)-LEN(SUBSTITUTE(A1,d,""))-(n-1)))-1)
kde d je oddeľovač a n je počet slov, ktoré sa majú odstrániť.
