Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du fjerner tall fra teksten i en celle i Excel og Google Sheets.
Vi vil diskutere to forskjellige formler for fjerning av tall fra tekst i Excel.
BYTTE Funksjonsformel
Vi kan bruke en formel basert på SUBSTITUTE -funksjonen. Det er en lang formel, men det er en av de enkleste måtene å fjerne tall fra en alfanumerisk streng.
I denne formelen har vi nestet SUBSTITUTE -funksjoner 10 ganger, slik:
| 1 | = UTSKIFTNING (UTSKIFTNING (UTSKIFTNING (UTSKIFTNING (UTSKIFTNING (UTSKIFTNING (ERSTATNING (UTSKIFTNING (UTSKIFTNING (UTSKIFTNING (SKJEMA (Bytte 1, ""), 2, ""), 3, ""), 4, ""), "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Array TEXTJOIN formel
For å fjerne tall fra alfanumeriske strenger, kan vi også bruke en kompleks matriseformel som består av funksjonene TEXTJOIN, MID, ROW og INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1), ""))}} |
Merk: TEXTJOIN er en ny Excel -funksjon tilgjengelig i Excel 2022+ og Office 365.
Dette er en kompleks formel, så vi deler den i trinn for å forstå den bedre.
Trinn 1
MID -funksjonen brukes til å trekke ut den alfanumeriske strengen basert på argumentene start_num og num_chars.
For start_num -argumentet i MID -funksjonen, bruker vi den resulterende matriselisten fra funksjonene RAD og INDIREKT.
| 1 | = RAD (INDIREKT ("1:" og LENG (B3))) |
Og for num-chars-argumentet setter vi 1. Etter å ha satt argumentene i MID-funksjonen, vil den returnere en matrise.
| 1 | {= MIDT (B3, RAD (INDIRECT ("1:" og LEN (B3))), 1)} |
Steg 2
Vi legger til null til hver verdi i den resulterende matrisen (som vi får fra ovennevnte MID -funksjon). I excel, hvis vi legger til tall i ikke-numeriske tegn, får vi en #VERDI! Feil. Så, etter å ha lagt til 0 i matrisen ovenfor, får vi en rekke tall og #Value! Feil.
| 1 | {= MIDT (B3, RAD (INDIRECT ("1:" og LEN (B3))), 1) +0} |
Trinn 3
Etter å ha lagt til 0, blir den resulterende matrisen satt inn i ISERR -funksjonen. Som vi vet returnerer ISERR-funksjonen SANN for feil og FALSK for ikke-feilverdier.
Så det vil gi en rekke SANN og FALSK, SANN for ikke-numeriske tegn og FALSK for tall.
| 1 | = ISERR (MIDT (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1) +0) |
Trinn 4
Nå legger vi til IF -funksjonen.
IF -funksjonen vil kontrollere resultatet av ISERR -funksjonen (trinn 3). Hvis verdien er SANN, returnerer den en rekke med alle tegnene i en alfanumerisk streng. For dette har vi lagt til en annen MID -funksjon uten å legge til null på slutten. Hvis verdien til IF -funksjonen er FALSE, returnerer den blank (“”).
På denne måten har vi en matrise som bare inneholder de ikke-numeriske tegnene i strengen.
| 1 | = HVIS (ISERR (MIDD (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1) +0), MIDD (B3, RAD (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
Trinn 5
Til slutt settes ovenstående matrise inn i TEKSTJOIN -funksjonen. TEKSTJOIN -funksjonen vil koble sammen alle tegnene i matrisen ovenfor og ignorere den tomme strengen.
Avgrenseren for denne funksjonen er satt til en tom streng ("") og ignorer_empty -argumentets verdi er angitt SANN.
Dette vil gi oss ønsket resultat, dvs. bare de ikke-numeriske tegnene i den alfanumeriske strengen.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1), ""))}} |
Merk: Dette er en matriseformel. Når du angir matriseformler i Excel 2022 eller tidligere, må du bruke CTRL + SKIFT + ENTER for å angi formelen i stedet for den vanlige TAST INN.
Du vet at du har angitt formelen riktig etter de krøllete parentesene som vises. IKKE skriv inn de krøllete parentesene manuelt, formelen fungerer ikke.
Med Office 365 (og antagelig versjoner av Excel etter 2022) kan du ganske enkelt angi formelen som vanlig.
TRIM -funksjon
Når tallene er fjernet fra strengen, kan vi ha ekstra mellomrom igjen. For å fjerne alle de etterfølgende og ledende mellomrommene og de ekstra mellomrommene mellom ord, kan vi bruke TRIM -funksjonen før hovedformelen, slik:
| 1 | = TRIM (C3) |
Fjern tall fra tekst i Google Regneark
Formelen for å fjerne tall fra teksten fungerer nøyaktig det samme i Google Regneark som i Excel:
