Obecný vzorec
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
souhrn
Chcete-li oddělit text a čísla, můžete použít vzorec založený na funkci FIND, MIN a LEN s funkcí LEFT nebo RIGHT podle toho, zda chcete extrahovat text nebo číslo. V zobrazeném příkladu je vzorec v C5:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
který vrací 7, pozici čísla 3 v řetězci „jablka30“.
Vysvětlení
Přehled
Vzorec vypadá složitě, ale mechanika je ve skutečnosti docela jednoduchá.
Stejně jako u většiny vzorců, které rozdělují nebo extrahují text, je klíčové najít polohu hledané věci. Jakmile máte pozici, můžete použít jiné funkce k extrahování toho, co potřebujete.
V tomto případě předpokládáme, že čísla a text jsou kombinovány a že číslo se objeví za textem. Z původního textu, který se zobrazí v jedné buňce, chcete rozdělit text a čísla do samostatných buněk, například takto:
| Originál | Text | Číslo |
| Jablka30 | Jablka | 30 |
| broskve24 | broskve | 24 |
| 12. pomeranče | pomeranče | 12 |
| broskve0 | broskve | 0 |
Jak je uvedeno výše, v tomto případě je klíčem najít počáteční pozici čísla, což můžete udělat s tímto vzorcem:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Jakmile budete mít pozici, extrahujete pouze text:
=LEFT(A1,position-1)
Chcete-li extrahovat pouze číslo, použijte:
=RIGHT(A1,LEN(A1)-position+1)
V prvním výše uvedeném vzorci používáme funkci FIND k vyhledání počáteční polohy čísla. Pro find_text používáme maticovou konstantu (0,1,2,3,4,5,6,7,8,9), což způsobí, že funkce FIND provede samostatné hledání každé hodnoty v maticové konstantě. Protože konstanta pole obsahuje 10 čísel, výsledkem bude pole s 10 hodnotami. Pokud je například původní text „jablka30“, výsledné pole bude:
(8,10,11,7,13,14,15,16,17,18)
Každé číslo v tomto poli představuje pozici položky v konstantě pole uvnitř původního textu.
Dále funkce MIN vrací nejmenší hodnotu v seznamu, což odpovídá pozici prvního čísla, které se objeví v původním textu. Funkce FIND v podstatě získá všechny pozice čísel a MIN nám dá pozici prvního čísla: všimněte si, že 7 je nejmenší hodnota v poli, což odpovídá pozici čísla 3 v původním textu.
Možná vás zajímá divná konstrukce pro within_text ve funkci find:
B5&"0123456789"
Tato část vzorce spojuje všechna možná čísla 0-9 s původním textem v B5. FIND bohužel nevrátí nulu, když není nalezena hodnota, takže je to jen chytrý způsob, jak se vyhnout chybám, které by mohly nastat, když číslo není nalezeno.
V tomto příkladu, protože předpokládáme, že se číslo v původním textu objeví vždy na druhém místě, funguje dobře, protože MIN vynutí pouze nejmenší nebo první výskyt čísla, které má být vráceno. Dokud se v původním textu objeví číslo, bude tato pozice vrácena.
Pokud původní text neobsahuje žádná čísla, bude vrácena falešná pozice rovnající se délce původního textu + 1. S touto falešnou pozicí výše uvedený LEVÝ vzorec stále vrátí text a pravý vzorec vrátí prázdný řetězec ("").
