Stiahnite si ukážkový zošit
Tento návod bude Ukážeme vám, ako nájsť a extrahovať číslo z textového reťazca v Exceli a Tabuľkách Google.
Nájdite a extrahujte číslo z reťazca
Niekedy môžu vaše údaje obsahovať čísla a text a chcete extrahovať číselné údaje.
Ak je číselná časť na pravej alebo ľavej strane reťazca, je ľahké rozdeliť číslo a text. Ak sú však čísla vo vnútri reťazca, t.j. medzi dvoma textovými reťazcami, budete musieť použiť oveľa komplikovanejší vzorec (zobrazený nižšie).
TEXTJOIN - extrahujte čísla v programe Excel 2016+
V programe Excel 2016 bola zavedená funkcia TEXTJOIN, ktorá dokáže extrahovať čísla z ľubovoľného miesta v textovom reťazci. Tu je vzorec:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Pozrime sa, ako tento vzorec funguje.
Funkcie ROW, INDIRECT a LEN vrátia pole čísel zodpovedajúcich každému znaku vo vašom alfanumerickom reťazci. V našom prípade má „Monday01Day“ 11 znakov, takže funkcia ROW potom vráti pole {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", PRAVDA, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Ďalej funkcia MID extrahuje každý znak z textového reťazca a vytvorí pole obsahujúce váš pôvodný reťazec:
| 1 | = TEXTJOIN ("", PRAVDA, IFERROR (({"M"; "o"; "n"; "d"; a ";" y ";" 0 ";" 1 ";" D ";" a ";" y "}*1)," "))) |
Vynásobením každej hodnoty v poli hodnotou 1 sa vytvorí pole obsahujúce chyby, ak bol znak poľa textový:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1;#VALUE!;#VALUE!;#VALUE !}), ""))) |
Ďalej funkcia IFERROR odstráni chybové hodnoty:
| 1 | = TEXTJOIN ("", PRAVDA, {""; "" ""; ""; ""; ""; 0; 1; ""; ""; ""}}) |
Zostáva iba funkcia TEXTJOIN, ktorá spája zvyšné čísla.
Všimnite si toho, že vzorec vám poskytne všetky číselné znaky spoločne z vášho reťazca. Ak je napríklad váš alfanumerický reťazec Monday01Day01, výsledkom bude 0101.
Extrahovať čísla - pred Excelom 2016
Pred Excelom 2016 ste mohli na extrahovanie čísel z textu použiť oveľa komplikovanejšiu metódu. Na určenie prvej polohy číselnej časti a prvej polohy textovej časti za číslom môžete použiť funkciu NÁJSŤ spolu s funkciami IFERROR a MIN. Potom jednoducho extrahujeme číselnú časť pomocou funkcie MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (VYHĽADÁVAŤ ({"a") , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Poznámka: Toto je vzorec poľa, vzorec musíte zadať stlačením klávesov CTRL + SHIFT + ENTER, namiesto iba klávesu ENTER.
Pozrime sa krok za krokom, ako tento vzorec funguje.
Nájdite prvé číslo
Na vyhľadanie počiatočnej polohy čísla môžeme použiť funkciu NÁJSŤ.
| 1 | = MIN (IFERROR (NÁJDETE ((1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Pre argument find_text funkcie FIND používame konštantu poľa {0,1,2,3,4,5,6,7,8,9}, vďaka ktorej funkcia FIND vykonáva samostatné vyhľadávanie pre každú hodnotu v konštanta poľa.
Argument within_text funkcie FIND je v našom prípade Monday01Day, v ktorom 1 možno nájsť na pozícii 8 a 0 na pozícii 7, takže naše pole výsledkov bude: {8,#VALUE,#VALUE,#VALUE, #HODNOTA, #HODNOTA, #HODNOTA, #HODNOTA, #HODNOTA, 7}.
Pomocou funkcie IFERROR nahradíme chyby #HODNOTA číslom 999999999. Potom jednoducho hľadáme minimum v tomto poli a získame preto miesto prvého čísla (7).
Uvedomte si, že vyššie uvedený vzorec je maticový vzorec, na jeho spustenie musíte stlačiť Ctrl+Shift+Enter.
Nájdite prvý textový znak za číslom
Podobne ako pri vyhľadávaní prvého čísla v reťazci, aj pomocou funkcie NÁJSŤ zistíme, kde sa text začína znova po čísle, ktoré dobre využíva aj argument start_num funkcie.
| 1 | = MIN (IFERROR (NÁJDETE ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Tento vzorec funguje veľmi podobne ako predchádzajúci použitý na lokalizáciu prvého čísla, iba v našej konštante poľa používame písmená, a preto čísla spôsobujú chyby #HODNOTA. Upozorňujeme, že vyhľadávanie začíname až po pozícii určenej pre prvé číslo (toto bude argument start_num) a nie od začiatku reťazca.
Ak chcete použiť vyššie uvedený vzorec, nezabudnite stlačiť Ctrl+Shift+Enter.
Nezostáva nič iné, ako to dať všetko dohromady.
Extrahujte číslo z dvoch textov
Akonáhle máme počiatočnú pozíciu číselnej časti a potom začiatok textovej časti, jednoducho použijeme funkciu MID na extrahovanie požadovanej číselnej časti.
| 1 | = MID (B3, C3, D3-C3) |
Iná metóda
Bez podrobnejšieho vysvetlenia môžete číslo použiť aj na získanie čísla z reťazca.
| 1 | = SUMPRODUKT (MID (0 & B3, VEĽKÝ (INDEX (ISNUMBER (-MID (B3, ROW (NEPRIAMY ("1:" & LEN (B3))), 1)) 1*) )), 0), RIADOK (NEPRIAMY ("1:" & LEN (B3))))+1,1)*10^ROW (NEPRIAMY ("1:" & LEN (B3)))/10) |
Uvedomte si, že tento vzorec vyššie vám poskytne 0, ak v reťazci nie je nájdené žiadne číslo a úvodné nuly budú vynechané.
Nájdite a extrahujte číslo z reťazca do textu v Tabuľkách Google
Príklady uvedené vyššie fungujú v Tabuľkách Google rovnako ako v programe Excel.
