Generisk formel
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Resumé
For at fjerne numeriske tegn fra en tekststreng kan du bruge en formel baseret på TEXTJOIN-funktionen. I det viste eksempel er formlen i C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Forklaring
Excel har ikke en måde at kaste bogstaverne i en tekststreng til en matrix direkte i en formel. Som en løsning bruger denne formel MID-funktionen med hjælp fra ROW- og INDIRECT-funktionerne for at opnå det samme resultat. Formlen i C5, kopieret ned, er:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Dette ser ret kompliceret ud, men kernen er, at vi opretter en matrix med alle tegn i B5 og tester hvert tegn for at se, om det er et tal. I så fald kasserer vi værdien og erstatter den med en tom streng (""). Hvis ikke, tilføjer vi det ikke-numeriske tegn til et "behandlet" array. Endelig bruger vi TEXTJOIN-funktionen (ny i Excel 2019) til at sammenkæde alle tegn sammen og ignorere tomme værdier.
Arbejder indefra og ud, bruges MID-funktionen til at udtrække teksten i B5, et tegn ad gangen. Nøglen er ROW og INDIRECT uddraget her:
ROW(INDIRECT("1:100"))
der spinder et array op, der indeholder 100 numre som dette:
(1,2,3,4,5,6,7,8… .99,100)
Bemærk: 100 repræsenterer det maksimale antal tegn, der skal behandles. Skift, så det passer til dine data, eller brug LEN-funktionen som forklaret nedenfor.
Denne matrix går ind i MID-funktionen som start_num argument. For num_chars bruger vi 1.
MID-funktionen returnerer en matrix som denne:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Bemærk: ekstra elementer i arrayet fjernet for læsbarhed.
Til denne matrix tilføjer vi nul. Dette er et simpelt trick, der tvinger Excel til at tvinge tekst til et tal. Numeriske tekstværdier som "1", "2", "3", "4" osv. Konverteres uden fejl, men ikke-numeriske værdier mislykkes og kaster en #VALUE-fejl. Vi bruger IF-funktionen med ISERR-funktionen til at fange disse fejl. Når vi ser en fejl, ved vi, at vi har et ikke-numerisk tegn, så vi bringer det tegn ind i det behandlede array med en anden MID-funktion:
MID(B5,ROW(INDIRECT("1:100")),1)
Hvis vi ikke får en fejl, ved vi, at vi har et tal, så vi indsætter en tom streng ("") i arrayet i stedet for nummeret.
Det endelige array-resultat går ind i TEXTJOIN-funktionen som text1-argumentet. Til afgrænsning bruger vi en tom streng ("") og for ignorere_forsøger leverer vi SAND. TEXTJOIN sammenkæder derefter alle ikke-tomme værdier i arrayet og returnerer resultatet.
Præcis array længde
I stedet for at hardcode et tal som 100 i INDIRECT, kan du bruge LEN-funktionen til at oprette en matrix med det faktiske antal tegn i cellen på denne måde:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN returnerer antallet af tegn i cellen som et tal, der bruges i stedet for 100. Dette gør det muligt for formlen at skalere op til et hvilket som helst antal tegn automatisk.
Fjernelse af ekstra plads
Når du stripper numeriske tegn, kan du have ekstra mellemrumstegn tilovers. For at fjerne forreste og bageste mellemrum og normalisere mellemrum mellem ord kan du pakke formlen vist på denne side inde i TRIM-funktionen:
=TRIM(formula)
Med SEKVENS
I Excel 365 kan den nye sekvens-funktion erstatte ROW + INDIRECT-koden ovenfor:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Her bruger vi SEQUENCE + LEN til at opbygge en matrix med den rigtige længde i et trin.
Med LET
Vi kan yderligere strømline denne formel med LET-funktionen. Fordi arrayet oprettes to gange ovenfor med SEQUENCE og LEN, kan vi definere array som en variabel og oprette det kun en gang:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Her indstilles array- værdien kun en gang og bruges derefter to gange i MID-funktionen.
