Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan lehet számokat eltávolítani a szövegből egy cellában az Excel és a Google Táblázatokban.
Két különböző képletet fogunk megvitatni a számok eltávolításáról a szövegből Excelben.
PÓTLÁS Funkcióképlet
Használhatunk a SUBSTITUTE függvényen alapuló képletet. Ez egy hosszú képlet, de ez az egyik legegyszerűbb módja a számok eltávolításának egy alfanumerikus karakterláncból.
Ebben a képletben 10 -szer ágyaztuk be a SUBSTITUTE függvényeket, például:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (B3,1, ")))))), 2," "), 3," "), 4," "," "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Tömb TEXTJOIN képlet
A számok alfanumerikus karakterláncokból való eltávolításához használhatunk egy komplex tömbképletet is, amely a TEXTJOIN, MID, ROW és INDIRECT függvényekből áll.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, SOR (KÖZVETLEN ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Megjegyzés: A TEXTJOIN egy új Excel -funkció, amely elérhető az Excel 2022+ és az Office 365 rendszerben.
Ez egy összetett képlet, ezért lépésre osztjuk, hogy jobban megértsük.
1. lépés
A MID függvény az alfanumerikus karakterlánc kibontására szolgál a start_num és a num_chars argumentumok alapján.
A MID függvény start_num argumentumához a ROW és INDIRECT függvényekből származó tömblistát fogjuk használni.
| 1 | = SOR (KÖZVETLEN ("1:" & LEN (B3))) |
A num-chars argumentumhoz pedig az 1. értéket adjuk. Miután az argumentumokat a MID függvénybe helyezte, tömböt ad vissza.
| 1 | {= KÖZÉP (B3, SOR (KÖZVETLEN ("1:" & LEN (B3))), 1)} |
2. lépés
A kapott tömb minden értékéhez nullát adunk (amit a fenti MID függvényből kapunk). Excelben, ha számokat ad hozzá nem numerikus karakterekhez, akkor #ÉRTÉKET kapunk! Hiba. Tehát, miután hozzáadtuk a 0 -t a fenti tömbhöz, számtömböt és #értéket kapunk! Hibák.
| 1 | {= MID (B3, SOR (KÖZVETLEN ("1:" & LEN (B3)))), 1) +0} |
3. lépés
A 0 hozzáadása után a kapott tömb az ISERR függvénybe kerül. Mint tudjuk, az ISERR függvény az IGAZ értéket adja vissza a hibákhoz, és a HAMIS értéket a nem hibaértékekhez.
Tehát az IGAZ és HAMIS, az IGAZ a nem numerikus karakterek és a HAMIS a számok tömbjét adja meg.
| 1 | = ISERR (MID (B3, SOR (KÖZVETLEN ("1:" & LEN (B3)))), 1) +0) |
4. lépés
Most hozzáadjuk az IF függvényt.
Az IF funkció ellenőrzi az ISERR funkció eredményét (3. lépés). Ha értéke IGAZ, akkor egy alfanumerikus karakterlánc összes karakteréből álló tömböt ad vissza. Ehhez hozzáadtunk egy másik MID függvényt anélkül, hogy a végén nullát adtunk volna. Ha az IF függvény értéke HAMIS, akkor üresen tér vissza („”).
Ily módon olyan tömbünk lesz, amely csak a karakterlánc nem numerikus karaktereit tartalmazza.
| 1 | = HA (ISERR (KÖZEP (B3, SOR (KÖZVETLEN ("1:" & LEN (B3))))), 1) +0), KÖZÉP (B3, SOR (KÖZVETLEN ("1:" & LEN (B3)))), 1 ), "") |
5. lépés
Végül a fenti tömb a TEXTJOIN függvénybe kerül. A TEXTJOIN függvény összekapcsolja a fenti tömb összes karakterét, és figyelmen kívül hagyja az üres karakterláncot.
Ennek a függvénynek a határolója üres karakterlánc („”), és az ignore_empty argumentum értéke IGAZ.
Ez megadja a kívánt eredményt, azaz csak az alfanumerikus karakterlánc nem numerikus karaktereit.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, SOR (KÖZVETLEN ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Megjegyzés: Ez egy tömb képlet. Amikor tömbképleteket ír be az Excel 2022 vagy korábbi verziójában, a CTRL + SHIFT + ENTER hogy írja be a képletet a rendes helyett BELÉP.
A megjelenő göndör zárójelekből tudni fogja, hogy helyesen írta be a képletet. NE írja be kézzel a göndör zárójeleket, a képlet nem fog működni.
Az Office 365 (és feltehetően az Excel 2022 utáni verziói) esetén egyszerűen beírhatja a képletet, mint általában.
TRIM funkció
Amikor a számokat eltávolítjuk a karakterláncból, előfordulhat, hogy további szabad helyek maradnak. Az összes záró és vezető szóköz, valamint a szavak közötti szóközök eltávolításához használhatjuk a TRIM függvényt a fő képlet előtt, így:
| 1 | = TRIM (C3) |
Számok eltávolítása a Google Táblázatok szövegéből
A számok szövegből való eltávolítására szolgáló képlet pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
