Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako odstrániť čísla z textu v bunke v Exceli a Tabuľkách Google.
Budeme diskutovať o dvoch rôznych vzorcoch na odstraňovanie čísel z textu v programe Excel.
SUBSTITUTE Funkčný vzorec
Môžeme použiť vzorec založený na funkcii SUBSTITUTE. Je to dlhý vzorec, ale je to jeden z najľahších spôsobov, ako odstrániť čísla z alfanumerického reťazca.
V tomto vzorci sme vnorili funkcie SUBSTITUTE 10 -krát takto:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE) "), 6," "), 7," ")), 8," ")), 9," ")), 0," ") |
Pole vzorec TEXTJOIN
Na odstránenie čísel z alfanumerických reťazcov môžeme použiť aj komplexný vzorec poľa, ktorý pozostáva z funkcií 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á funkcia Excelu, ktorá je k dispozícii v Exceli 2022+ a Office 365.
Toto je komplexný vzorec, preto ho rozdelíme do krokov, aby sme ho lepšie pochopili.
Krok 1
Funkcia MID sa používa na extrahovanie alfanumerického reťazca na základe argumentov start_num a num_chars.
Pre argument start_num vo funkcii MID použijeme výsledný zoznam polí z funkcií ROW a INDIRECT.
| 1 | = ROW (NEPRIAMY ("1:" & LEN (B3))) |
A pre argument num-chars dáme 1. Po vložení argumentov do funkcie MID vráti pole.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Krok 2
Ku každej hodnote vo výslednom poli (ktoré získame z vyššie uvedenej funkcie MID) pripočítame nulu. V Exceli, ak pripočítate čísla k nečíselným znakom, dostaneme #HODNOTU! Chyba. Takže po pridaní 0 do vyššie uvedeného poľa získame pole čísel a #Hodnotu! Chyby.
| 1 | {= MID (B3, ROW (NEPRIAMY ("1:" & LEN (B3))), 1) +0} |
Krok 3
Po sčítaní 0 sa výsledné pole vloží do funkcie ISERR. Ako vieme, funkcia ISERR vracia hodnotu TRUE pre chyby a FALSE pre hodnoty bez chýb.
Poskytne teda pole TRUE a FALSE, TRUE pre nečíselné znaky a FALSE pre čísla.
| 1 | = ISERR (MID (B3, ROW (NEPRIAMY ("1:" & LEN (B3))), 1) +0) |
Krok 4
Teraz pridáme funkciu IF.
Funkcia IF skontroluje výsledok funkcie ISERR (krok 3). Ak je jeho hodnota PRAVDA, vráti pole všetkých znakov alfanumerického reťazca. Za týmto účelom sme pridali ďalšiu funkciu MID bez toho, aby sme na konci pridali nulu. Ak je hodnota funkcie IF NEPRAVDA, vráti prázdne miesto („“).
Týmto spôsobom budeme mať pole, ktoré obsahuje iba nečíselné znaky reťazca.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1 ), "") |
Krok 5
Nakoniec je vyššie uvedené pole vložené do funkcie TEXTJOIN. Funkcia TEXTJOIN spojí všetky znaky vyššie uvedeného poľa a prázdny reťazec bude ignorovať.
Oddeľovač pre túto funkciu je nastavený na prázdny reťazec („“) a hodnota argumentu ignore_empty je zadaná ako PRAVDA.
To nám poskytne požadovaný výsledok, tj iba nečíselné znaky alfanumerického reťazca.
| 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 poľa. Pri zadávaní vzorcov poľa v programe Excel 2022 alebo staršom musíte použiť CTRL + SHIFT + ENTER zadať vzorec namiesto pravidelného VSTÚPIŤ.
Podľa zložených zátvoriek, ktoré sa zobrazia, budete vedieť, že ste vzorec zadali správne. NEZADÁVAJTE ručne zložené zátvorky, vzorec nebude fungovať.
S Office 365 (a pravdepodobne verziami Excelu po roku 2022) môžete vzorec jednoducho zadať ako obvykle.
Funkcia TRIM
Keď sú čísla z reťazca odstránené, môžu nám zostať ďalšie medzery. Ak chcete odstrániť všetky medzery na konci a na začiatku a medzery medzi slovami, môžeme pred hlavným vzorcom použiť funkciu TRIM:
| 1 | = TRIM (C3) |
Odstráňte čísla z textu v Tabuľkách Google
Vzorec na odstránenie čísel z textu funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
