Általános képlet
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Összegzés
A szöveges karakterláncból numerikus karakterek eltávolításához használhatja a TEXTJOIN függvényen alapuló képletet. A bemutatott példában a C5 képlete a következő:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Magyarázat
Az Excelnek nincs módja arra, hogy a szöveges karakterlánc betűit közvetlenül egy képlet tömbjébe öntse. Megkerülő megoldásként ez a képlet a MID függvényt használja, a ROW és INDIRECT függvények segítségével ugyanazon eredmény elérése érdekében. A C5 képlete, lemásolva:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Ez elég bonyolultnak tűnik, de a lényeg az, hogy létrehozunk egy tömböt az összes karakterből a B5-ben, és minden karaktert tesztelünk, hogy ez szám-e. Ha igen, akkor elvetjük az értéket, és egy üres karakterlánccal ("") helyettesítjük. Ha nem, akkor hozzáadjuk a nem numerikus karaktert egy "feldolgozott" tömbhöz. Végül a TEXTJOIN funkciót (új az Excel 2019-ben) használjuk az összes karakter összefűzéséhez, figyelmen kívül hagyva az üres értékeket.
Belülről kifelé haladva a MID függvény segítségével a B5-ös szöveget egyszerre egyetlen karakterrel vonják ki. A kulcs itt a ROW és INDIRECT kódrészlet:
ROW(INDIRECT("1:100"))
amely felpörget egy 100 ilyen számot tartalmazó tömböt:
(1,2,3,4,5,6,7,8… .99,100)
Megjegyzés: 100 a maximálisan feldolgozható karakterek száma. Változtassa meg az adatait, vagy használja a LEN funkciót az alábbiakban leírtak szerint.
Ez a tömb a MID függvénybe kerül mint start_num argumentum. A num_chars esetében az 1-et használjuk.
A MID függvény egy ilyen tömböt ad vissza:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Megjegyzés: a tömbből további elemeket távolítottak el az olvashatóság érdekében.
Ehhez a tömbhöz nullát adunk. Ez egy egyszerű trükk, amely arra kényszeríti az Excel-t, hogy a szöveget egy számra kényszerítse. A numerikus szöveges értékeket, például az "1", "2", "3", "4" stb. Hibátlanul konvertáljuk, de a nem numerikus értékek meghiúsulnak és #VALUE hibát vetnek fel. Az IF függvényt az ISERR függvénnyel használjuk ezen hibák felfogására. Ha hibát látunk, tudjuk, hogy nem numerikus karakterünk van, ezért ezt a karaktert egy másik MID funkcióval hozzuk be a feldolgozott tömbbe:
MID(B5,ROW(INDIRECT("1:100")),1)
Ha nem kap hibát, tudjuk, hogy van számunk, ezért egy üres karakterláncot ("") illesztünk a tömbbe a szám helyett.
A végső tömb eredménye a TEXTJOIN függvénybe kerül text1 argumentumként. A határolóhoz egy üres karakterláncot ("") használunk, az ignore_empty esetében pedig az IGAZ értéket. Ezután a TEXTJOIN összefűzi a tömb összes nem üres értékét, és visszaadja az eredményt.
Pontos tömbhossz
Ahelyett, hogy egy 100-as számot keményen kódolna az INDIRECT-be, a LEN függvény segítségével tömböt készíthet a cellában a karakterek tényleges számával, így:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
A LEN a cellában lévő karakterek számát adja vissza számként, amelyet 100 helyett használnak. Ez lehetővé teszi a képlet tetszőleges számú karakterre történő automatikus méretezést.
További hely eltávolítása
Ha numerikus karaktereket vetít le, előfordulhat, hogy több szóköz marad. Az elülső és a záró szóköz levágására, valamint a szavak közötti szóközök normalizálására az ezen az oldalon látható képletet beburkolhatja a TRIM funkcióba:
=TRIM(formula)
SZekvenciával
Az Excel 365-ben az új SEQUENCE függvény helyettesítheti a fenti ROW + INDIRECT kódot:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Itt a SEQUENCE + LEN segítségével egy lépésben felépítünk egy megfelelő hosszúságú tömböt.
LET-tel
Ezt a képletet tovább egyszerűsíthetjük a LET függvénnyel. Mivel a tömböt kétszer hoztuk létre a SEQUENCE és a LEN segítségével, definiálhatjuk a tömböt változóként, és csak egyszer hozhatjuk létre:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Itt a tömb értékét csak egyszer állítják be, majd kétszer használják a MID függvényen belül.
