Generisk formel
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Sammendrag
For å fjerne numeriske tegn fra en tekststreng, kan du bruke en formel basert på TEXTJOIN-funksjonen. I eksemplet vist er formelen i C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Forklaring
Excel har ikke en måte å kaste bokstavene i en tekststreng til en matrise direkte i en formel. Som en løsning bruker denne formelen MID-funksjonen, med hjelp fra ROW og INDIRECT-funksjonene for å oppnå det samme resultatet. Formelen i C5, kopiert ned, er:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Dette ser ganske komplisert ut, men kjernen er at vi lager en rekke alle tegn i B5, og tester hvert tegn for å se om det er et tall. I så fall forkaster vi verdien og erstatter den med en tom streng (""). Hvis ikke, legger vi det ikke-numeriske tegnet til en "behandlet" matrise. Til slutt bruker vi TEXTJOIN-funksjonen (ny i Excel 2019) for å sammenkoble alle tegn sammen, ignorere tomme verdier.
MID-funksjonen fungerer fra innsiden og ut og brukes til å trekke ut teksten i B5, ett tegn om gangen. Nøkkelen er ROW og INDIRECT kodebiten her:
ROW(INDIRECT("1:100"))
som spinner opp en matrise som inneholder 100 tall som dette:
(1,2,3,4,5,6,7,8… .99,100)
Merk: 100 representerer maksimalt antall tegn som skal behandles. Endre for å passe dine data, eller bruk LEN-funksjonen som forklart nedenfor.
Denne matrisen går inn i MID-funksjonen som argumentet start_num . For num_chars bruker vi 1.
MID-funksjonen returnerer en matrise som dette:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Merk: ekstra elementer i matrisen fjernet for lesbarhet.
I denne matrisen legger vi til null. Dette er et enkelt triks som tvinger Excel til å tvinge tekst til et tall. Numeriske tekstverdier som "1", "2", "3", "4" osv. Konverteres uten feil, men ikke-numeriske verdier vil mislykkes og kaste en #VALUE-feil. Vi bruker IF-funksjonen med ISERR-funksjonen for å fange disse feilene. Når vi ser en feil, vet vi at vi har et ikke-numerisk tegn, så vi tar det tegnet inn i den behandlede matrisen med en annen MID-funksjon:
MID(B5,ROW(INDIRECT("1:100")),1)
Hvis du ikke får feil, vet vi at vi har et tall, så vi setter inn en tom streng ("") i matrisen i stedet for nummeret.
Det endelige matriseresultatet går inn i TEXTJOIN-funksjonen som text1-argumentet. For avgrensning bruker vi en tom streng ("") og for ignore_empty leverer vi SANT. TEXTJOIN sammenkobler deretter alle ikke-tomme verdier i matrisen og returnerer resultatet.
Presis matriselengde
I stedet for å hardkode et tall som 100 i INDIRECT, kan du bruke LEN-funksjonen til å bygge en matrise med det faktiske antall tegn i cellen slik:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN returnerer antall tegn i cellen som et tall, som brukes i stedet for 100. Dette gjør at formelen kan skaleres opp til et hvilket som helst antall tegn automatisk.
Fjerner ekstra plass
Når du striper numeriske tegn, kan du ha ekstra mellomromstegn til overs. For å fjerne ledende og etterfølgende mellomrom og normalisere mellomrom mellom ord, kan du pakke inn formelen som vises på denne siden i TRIM-funksjonen:
=TRIM(formula)
Med SEKVENS
I Excel 365 kan den nye SEKVENS-funksjonen erstatte ROW + INDIRECT-koden ovenfor:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Her bruker vi SEKVENS + LENGE til å bygge en matrise med riktig lengde i ett trinn.
Med LET
Vi kan videre effektivisere denne formelen med LET-funksjonen. Fordi matrisen er opprettet to ganger over med SEQUENCE og LEN, kan vi definere matrisen som en variabel, og opprette den bare en gang:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Her settes verdien av matrisen bare én gang, og brukes deretter to ganger i MID-funksjonen.
