Splošna formula
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Povzetek
Če želite iz besedilnega niza odstraniti številske znake, lahko uporabite formulo, ki temelji na funkciji TEXTJOIN. V prikazanem primeru je formula v C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Pojasnilo
Excel ne more oddati črk v besedilnem nizu v matriko neposredno v formuli. Kot rešitev je v tej formuli uporabljena funkcija MID s pomočjo funkcij ROW in INDIRECT za doseganje enakih rezultatov. Kopirana formula v C5 je:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
To se zdi precej zapleteno, bistvo pa je v tem, da v B5 ustvarimo vrsto vseh znakov in preizkusimo vsak znak, da ugotovimo, ali gre za številko. V tem primeru vrednost zavržemo in jo zamenjamo s praznim nizom (""). V nasprotnem primeru v "obdelano" matriko dodamo neštevilski znak. Na koncu uporabimo funkcijo TEXTJOIN (novo v Excelu 2019), da združimo vse znake skupaj, pri čemer prezremo prazne vrednosti.
Funkcija MID, ki deluje od znotraj navzven, se uporablja za izvlečenje besedila v B5, en znak naenkrat. Ključ je ROW in INDIRECT delček tukaj:
ROW(INDIRECT("1:100"))
ki vrti matriko, ki vsebuje 100 številk, kot je ta:
(1,2,3,4,5,6,7,8… .99.100)
Opomba: 100 predstavlja največje število znakov za obdelavo. Spremenite, da ustreza vašim podatkom, ali uporabite funkcijo LEN, kot je razloženo spodaj.
Ta matrika gre v funkcijo MID kot argument start_num . Za num_chars uporabljamo 1.
Funkcija MID vrne matriko, kot je ta:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Opomba: dodatni elementi v polju so odstranjeni zaradi berljivosti.
Temu polju dodamo ničlo. To je preprost trik, ki prisili Excel, da prisili besedilo v številko. Številske besedilne vrednosti, kot so "1", "2", "3", "4" itd., Se pretvorijo brez napak, vendar neštevilske vrednosti ne bodo uspele in bodo povzročile napako #VALUE. Za lovljenje teh napak uporabljamo funkcijo IF s funkcijo ISERR. Ko opazimo napako, vemo, da imamo neštevilski znak, zato ga vnesemo v obdelano matriko z drugo funkcijo MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Če ne dobimo napake, vemo, da imamo številko, zato v polje namesto števila vstavimo prazen niz ("").
Končni rezultat polja preide v funkcijo TEXTJOIN kot argument text1. Za ločilo uporabljamo prazen niz (""), za ignore_empty pa TRUE. Nato TEXTJOIN združi vse neprazne vrednosti v matriki in vrne rezultat.
Natančna dolžina polja
Namesto da trdo kodirate število, kot je 100, v INDIRECT, lahko s funkcijo LEN zgradite matriko z dejanskim številom znakov v celici, kot je ta:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN vrne število znakov v celici kot število, ki se uporabi namesto 100. To omogoča, da se formula samodejno poveča na poljubno število znakov.
Odstranjevanje odvečnega prostora
Ko odstranite številske znake, vam lahko ostanejo dodatni presledki. Če želite odstraniti vodilni in zadnji presledek ter normalizirati presledke med besedami, lahko formulo, prikazano na tej strani, zavijete v funkcijo TRIM:
=TRIM(formula)
S SEQUENCE
V Excelu 365 lahko nova funkcija SEQUENCE nadomesti zgornjo vrstico ROW + INDIRECT:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Tu uporabimo SEQUENCE + LEN, da v enem koraku sestavimo matriko pravilne dolžine.
Z LET
To formulo lahko nadalje poenostavimo s funkcijo LET. Ker je matrika dvakrat zgoraj ustvarjena z SEQUENCE in LEN, lahko matriko definiramo kot spremenljivko in jo ustvarimo samo enkrat:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Tu je vrednost matrike nastavljena samo enkrat, nato pa dvakrat uporabljena znotraj funkcije MID.
