Stáhněte si ukázkový sešit
Tento tutoriál bude Ukážeme vám, jak najít a extrahovat číslo z textového řetězce v Excelu a Tabulkách Google.
Najděte a extrahujte číslo z řetězce
Někdy mohou vaše data obsahovat čísla a text a chcete extrahovat číselná data.
Pokud je číselná část na pravé nebo levé straně řetězce, je poměrně snadné rozdělit číslo a text. Pokud jsou však čísla uvnitř řetězce, tj. Mezi dvěma textovými řetězci, budete muset použít mnohem složitější vzorec (viz níže).
TEXTJOIN - extrahujte čísla v aplikaci Excel 2016+
V aplikaci Excel 2016 byla zavedena funkce TEXTJOIN, která dokáže extrahovat čísla z libovolného místa v textovém řetězci. Zde je vzorec:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Podívejme se, jak tento vzorec funguje.
Funkce ROW, INDIRECT a LEN vrací řadu čísel odpovídajících každému znaku ve vašem alfanumerickém řetězci. V našem případě má „Monday01Day“ 11 znaků, takže funkce ROW pak vrátí 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), "")) |
Dále funkce MID extrahuje každý znak z textového řetězce a vytvoří pole obsahující váš původní řetězec:
| 1 | = TEXTJOIN ("", PRAVDA, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," "))) |
Vynásobením každé hodnoty v poli hodnotou 1 vytvoříte pole obsahující chyby Pokud byl znak pole textový:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE !}), "")) |
Dále funkce IFERROR odstraní chybové hodnoty:
| 1 | = TEXTJOIN ("", TRUE, {""; "" ""; ""; ""; ""; 0; 1; ""; ""; ""}}) |
Ponecháte pouze funkci TEXTJOIN, která spojí zbývající čísla dohromady.
Vzorec vám poskytne všechny číselné znaky společně z vašeho řetězce. Pokud je například váš alfanumerický řetězec Monday01Day01, výsledkem bude 0101.
Extrahovat čísla - před aplikací Excel 2016
Před aplikací Excel 2016 jste mohli použít mnohem složitější metodu k extrahování čísel z textu. Pomocí funkce NAJÍT spolu s funkcemi IFERROR a MIN můžete určit jak první pozici číselné části, tak první pozici textové části za číslem. Poté jednoduše extrahujeme číselnou část funkcí MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"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 pole, vzorec musíte zadat stisknutím kláves CTRL + SHIFT + ENTER, místo pouze ENTER.
Podívejme se krok za krokem, jak tento vzorec funguje.
Najděte číslo pěst
K vyhledání počáteční pozice čísla můžeme použít funkci NAJÍT.
| 1 | = MIN (IFERROR (NAJÍT ((1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Pro argument find_text funkce FIND používáme maticovou konstantu {0,1,2,3,4,5,6,7,8,9}, díky které funkce FIND provádí samostatné vyhledávání pro každou hodnotu v maticová konstanta.
Argument within_text funkce FIND je v našem případě Monday01Day, ve kterém 1 lze nalézt na pozici 8 a 0 na pozici 7, takže naše pole výsledků bude: {8,#VALUE,#VALUE,#VALUE, #HODNOTA, #HODNOTA, #HODNOTA, #HODNOTA, #HODNOTA, 7}.
Pomocí funkce IFERROR nahradíme chyby #HODNOTA číslem 999999999. Pak jednoduše hledáme minimum v tomto poli a získáme tedy místo prvního čísla (7).
Všimněte si, že výše uvedený vzorec je maticový vzorec, musíte jej spustit stisknutím Ctrl+Shift+Enter.
Najděte první textový znak za číslem
Podobně jako při hledání prvního čísla v řetězci používáme funkci FIND k určení, kde text začíná znovu poté, co číslo dobře využije také argument start_num funkce.
| 1 | = MIN (IFERROR (NAJÍT ({"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 velmi podobně jako předchozí, který se používá k vyhledání prvního čísla, pouze v naší konstantě pole používáme písmena, a proto čísla způsobují chyby #HODNOTA. Pamatujte, že hledání začneme až po pozici určené pro první číslo (toto bude argument start_num) a ne od začátku řetězce.
Pro použití výše uvedeného vzorce nezapomeňte stisknout Ctrl+Shift+Enter.
Nezbývá nic jiného, než to všechno dát dohromady.
Extrahujte číslo ze dvou textů
Jakmile máme počáteční pozici číselné části a poté začátek textové části, jednoduše použijeme funkci MID k extrahování požadované číselné části.
| 1 | = MID (B3, C3, D3-C3) |
Jiná metoda
Aniž byste to vysvětlovali podrobněji, můžete také použít níže uvedený složitý vzorec k získání čísla z řetězce.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)) 1))*ROW (INDIRECT ("1:" & LEN (B3) )), 0), ŘADA (NEPŘÍMÝ ("1:" & LEN (B3))))+1,1)*10^ŘADA (NEPŘÍMÝ ("1:" & LEN (B3)))/10) |
Uvědomte si, že výše uvedený vzorec vám poskytne 0, pokud v řetězci není nalezeno žádné číslo a že počáteční nuly budou vynechány.
Najděte a extrahujte číslo z řetězce do textu v Tabulkách Google
Výše uvedené příklady fungují v Tabulkách Google stejně jako v Excelu.
