Töltse le a példa munkafüzetet
Ez a bemutató lesz bemutatja, hogyan lehet megkeresni és kinyerni egy számot egy szöveges karakterláncból az Excelben és a Google Táblázatokban.
Szám keresése és kivonása a karakterláncból
Előfordulhat, hogy az adatok számokat és szöveget tartalmaznak, és ki szeretné vonni a számadatokat.
Ha a számrész a karakterlánc jobb vagy bal oldalán található, akkor viszonylag könnyű felosztani a számot és a szöveget. Ha azonban a számok a karakterláncon belül vannak, azaz két szöveg karakterlánc között, akkor sokkal bonyolultabb képletet kell használnia (lásd alább).
TEXTJOIN - Számok kivonása az Excel 2016+ -ban
Az Excel 2016 -ban bevezetésre került a TEXTJOIN függvény, amely a szöveges karakterlánc bárhonnan kinyerheti a számokat. Íme a képlet:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, SOR (KÖZVETLEN ("1:" & LEN (B3))), 1)*1), "")) |
Lássuk, hogyan működik ez a képlet.
A SOR, KÖZVETLEN és LEN függvény az alfanumerikus karakterlánc minden karakterének megfelelő számtömböt ad vissza. Esetünkben a „Monday01Day” 11 karakterből áll, így a ROW függvény visszaadja az {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11} tömböt.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Ezután a MID függvény kivon minden karaktert a szöveges karakterláncból, létrehozva egy tömböt, amely tartalmazza az eredeti karakterláncot:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
A tömb minden értékét 1 -gyel megszorozva hibát tartalmazó tömb jön létre, ha a tömb karaktere szöveg volt:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE !}), "")) |
Ezután az IFERROR függvény eltávolítja a hibaértékeket:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Csak a TEXTJOIN függvényt hagyja el, amely összekapcsolja a többi számot.
Ne feledje, hogy a képlet megadja az összes numerikus karaktert a karakterláncból. Például, ha az alfanumerikus karakterlánc hétfő01Day01, akkor 0101 értéket kap.
Számok kivonása - az Excel 2016 előtt
Az Excel 2016 előtt sokkal bonyolultabb módszert használhat a számok kinyerésére a szövegből. A FIND függvénnyel az IFERROR és MIN funkciókkal együtt meghatározhatja a számrész első pozícióját és a szöveges rész szám utáni első pozícióját. Ezután egyszerűen kivonjuk a számrészt a MID függvénnyel.
| 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", "én", "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))) |
Megjegyzés: Ez egy tömb képlet, a képletet a CTRL + SHIFT + ENTER billentyűkombinációval kell megadni, nem csak az ENTER billentyű helyett.
Lássuk lépésről lépésre, hogyan működik ez a képlet.
Keresse meg az Ököl számát
A FIND funkció segítségével megkereshetjük a szám kezdő pozícióját.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
A FIND függvény find_text argumentumához a {0,1,2,3,4,5,6,7,8,9} tömbállandót használjuk, ami miatt a FIND függvény külön keresést végez a a tömbállandó.
A FIND függvény belső_text argumentuma esetünkben a Monday01Day, amelyben 1 a 8, 0 pedig a 7 pozícióban található, így eredménytömbünk a következő lesz: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Az IFERROR függvény használatával lecseréljük az #ÉRTÉK hibákat 999999999 -re. Ezután egyszerűen megkeressük a minimumot ebben a tömbben, és így megkapjuk az első szám helyét (7).
Kérjük, vegye figyelembe, hogy a fenti képlet tömbképlet, aktiválásához nyomja meg a Ctrl+Shift+Enter billentyűkombinációt.
Keresse meg az első szöveg karaktert a szám után
A karakterláncon belüli első szám megkereséséhez hasonlóan a FIND függvénnyel határozzuk meg, hol kezdődik a szöveg újra a szám után, jól kihasználva a függvény start_num argumentumát is.
| 1 | = MIN (IFERROR (FIND ({"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)) |
Ez a képlet nagyon hasonlóan működik, mint az első, amelyet az első szám megkeresésére használtak, csak mi használunk betűket a tömbállandónkban, ezért a számok #ÉRTÉK hibákat okoznak. Kérjük, vegye figyelembe, hogy a keresést csak az első számhoz meghatározott pozíció után kezdjük el (ez lesz a start_num argumentum), és nem a karakterlánc elejétől.
Ne felejtse el megnyomni a Ctrl+Shift+Enter billentyűket a fenti képlet használatához.
Nincs más hátra, mint összeszedni ezt az egészet.
Szám kivonása két szövegből
Miután megvan a számrész kiindulási pozíciója és a szövegrész eleje, ezt követően egyszerűen a MID funkciót használjuk a kívánt számrész kivonásához.
| 1 | = MID (B3, C3, D3-C3) |
Más módszer
Anélkül, hogy részletesebben elmagyarázná, az alábbi komplex képlet segítségével is lekérheti a számot egy karakterláncból.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))))))))), 1))*ROW (INDIRECT ("1:" & LEN (B3)) )), 0), SOR (KÖZVETLEN ("1:" & LEN (B3))))+1,1)*10^SOR (KÖZVETLEN ("1:" & LEN (B3)))/10) |
Kérjük, vegye figyelembe, hogy a fenti képlet 0 -t ad, ha nem található szám a karakterláncban, és a kezdő nullák kimaradnak.
Szám keresése és kibontása karakterláncból szövegbe a Google Táblázatokban
A fenti példák ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
