Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak odstranit čísla z textu v buňce v Excelu a Tabulkách Google.
Budeme diskutovat o dvou různých vzorcích pro odstraňování čísel z textu v aplikaci Excel.
SUBSTITUTE Funkce Vzorec
Můžeme použít vzorec založený na funkci SUBSTITUTE. Je to dlouhý vzorec, ale je to jeden z nejjednodušších způsobů, jak odstranit čísla z alfanumerického řetězce.
V tomto vzorci jsme vnořili funkce SUBSTITUTE 10krát, například takto:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE)) "), 6," "), 7," ")), 8," "), 9," ")), 0," ") |
Pole vzorec TEXTJOIN
K odstranění čísel z alfanumerických řetězců můžeme použít také komplexní maticový vzorec, který se skládá z funkcí TEXTJOIN, MID, ROW a INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0)), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Poznámka: TEXTJOIN je nová funkce Excelu dostupná v Excelu 2022+ a Office 365.
Jedná se o složitý vzorec, proto jej rozdělíme do kroků, abychom jej lépe pochopili.
Krok 1
Funkce MID se používá k extrahování alfanumerického řetězce na základě argumentů start_num a num_chars.
Pro argument start_num ve funkci MID použijeme výsledný seznam polí z funkcí ROW a INDIRECT.
| 1 | = ŘÁDEK (NEPŘÍMÝ ("1:" & LEN (B3))) |
A pro argument num-chars dáme 1. Po vložení argumentů do funkce MID vrátí pole.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Krok 2
Ke každé hodnotě ve výsledném poli (které získáme z výše uvedené funkce MID) přidáme nulu. Pokud v Excelu přidáme čísla k nečíselným znakům, dostaneme #HODNOTU! Chyba. Takže po přidání 0 do výše uvedeného pole získáme řadu čísel a #Hodnotu! Chyby.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
Krok 3
Po přidání 0 se výsledné pole vloží do funkce ISERR. Jak víme, funkce ISERR vrací TRUE pro chyby a FALSE pro hodnoty bez chyb.
Poskytne tedy pole PRAVDA a NEPRAVDA, PRAVDA pro nečíselné znaky a NEPRAVDA pro čísla.
| 1 | = ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0) |
Krok 4
Nyní přidáme funkci IF.
Funkce IF zkontroluje výsledek funkce ISERR (krok 3). Pokud je jeho hodnota PRAVDA, vrátí pole všech znaků alfanumerického řetězce. Za tímto účelem jsme přidali další funkci MID bez přidání nuly na konci. Pokud je hodnota funkce IF NEPRAVDA, vrátí prázdné („“).
Tímto způsobem budeme mít pole, které obsahuje pouze nečíselné znaky řetězce.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
Krok 5
Nakonec je výše uvedené pole vloženo do funkce TEXTJOIN. Funkce TEXTJOIN spojí všechny znaky výše uvedeného pole a prázdný řetězec bude ignorovat.
Oddělovač pro tuto funkci je nastaven na prázdný řetězec („“) a hodnota argumentu ignore_empty je zadána jako PRAVDA.
Tím získáme požadovaný výsledek, tj. Pouze nečíselné znaky alfanumerického řetězce.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0)), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1), ""))} |
Poznámka: Toto je vzorec pole. Při zadávání vzorců pole v aplikaci Excel 2022 nebo dřívější musíte použít CTRL + SHIFT + ENTER místo pravidelného zadejte vzorec VSTUP.
Podle složených závorek, které se zobrazí, poznáte, že jste vzorec zadali správně. NEPOUŽÍVEJTE ručně složené závorky, vzorec nebude fungovat.
S Office 365 (a pravděpodobně verzemi Excelu po roce 2022) můžete jednoduše zadat vzorec jako obvykle.
Funkce TRIM
Když jsou čísla odstraněna z řetězce, mohou nám zbývat mezery navíc. Chcete -li odstranit všechny mezery na konci a na začátku a mezery mezi slovy, můžeme použít funkci TRIM před hlavním vzorcem, například takto:
| 1 | = OŘEZAT (C3) |
Odebrat čísla z textu v Tabulkách Google
Vzorec pro odstranění čísel z textu funguje v Tabulkách Google úplně stejně jako v Excelu:
