Obecný vzorec
=MID(A1,1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
souhrn
Chcete-li odstranit poslední slovo z textového řetězce, můžete použít vzorec založený na funkci MID s pomocí SUBSTITUTE, LEN a FIND. V zobrazeném příkladu je vzorec v buňce B5:
=MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)
Vysvětlení
Tento vzorec používá funkci MID k odstranění posledního slova z textového řetězce. Hlavní výzvou je zjistit, kde začíná poslední slovo.
Vzorec je trochu spletitý, ale kroky jsou jednoduché. Nejprve spočítáme, kolik mezer v textu existuje pomocí LEN a SUBSTITUTE. Dále použijeme poněkud nejasný argument „instance“ ve funkci SUBSTITUTE k nahrazení posledního prostoru vlnovkou (~). Nakonec použijeme FIND, abychom zjistili, kde je tilda, a pomocí funkce MID vše za vlnovkou zahodíme.
Při práci zevnitř ven počítáme pomocí funkcí LEN a SUBSTITUTE, kolik mezer se objeví v příštím:
LEN(B5)-LEN(SUBSTITUTE(B5," ","")) // returns 6
U textu v B5 to vrátí 6. Tato stránka podrobněji vysvětluje tuto část vzorce. To nám říká, že chceme oříznout text na šestém znaku mezery.
Poznámka: Pokud jsou slova oddělena více než jednou mezerou, budete chtít nejprve pomocí funkce TRIM normalizovat mezery.
Číslo 6 je poté zapojeno do jiného SUBSTITUTE jako „číslo instance“:
SUBSTITUTE(B5," ","~",6) // insert tilde
Zde nahradíme 6. instanci mezery ("") vlnovkou (~). Po spuštění SUBSTITUTE vrátí tento řetězec:
"It's been seven hours and fifteen~days"
Poznámka: Tildu (~) používáme pouze proto, že se jedná o zřídka se vyskytující znak. Můžete použít libovolný znak, který se vám líbí, pokud se neobjeví ve zdrojovém textu.
Dále použijeme funkci FIND k vyhledání vlnovky:
FIND("~","It's been seven hours and fifteen~days")-1
FIND vrací 34, protože tilda je 34. znak. Odečteme 1 od tohoto čísla, protože nechceme do konečného výsledku zahrnout poslední mezeru. Nyní můžeme vzorec zjednodušit na:
=MID(B5,1,33) // extract final text
Funkce MID poté vrátí znaky 1-33:
"It's been seven hours and fifteen"
S vlastním oddělovačem
Stejný vzorec lze použít s jiným oddělovačem. Chcete-li například odstranit celý text za posledním lomítkem „/“, můžete použít:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)
Odstraňte posledních n slov
Vzorec můžete upravit tak, aby odstranil poslední 2 slova, poslední 3 slova atd. Obecná forma je:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,d,"~",LEN(A1)-LEN(SUBSTITUTE(A1,d,""))-(n-1)))-1)
kde d je oddělovač a n je počet slov, která mají být odstraněna.
