Vi har lært hvordan vi fjerner numeriske verdier fra en celle i Excel 2016 og eldre. Formlene vi brukte var litt komplekse, men nå er Excel 2019 og 365 i spillet.
Excel 2019 og 365 introduserer noen nye funksjoner som letter oppgaven med å fjerne ikke -numeriske tegn og bare hente numeriske verdier i en ny celle. Vi vil bruke formler som kan hjelpe oss med å gjøre det, mer praktisk.
Generisk formel
| =TEKSTJOIN("",EKTE,IFERROR(MIDT(jumbled_text,SEKVENS(NumChars), 1) +0, "")) |
Jumbled_text: Dette er kildeteksten du vil trekke ut alle numeriske verdier fra.
NumChars: Dette er det totale antallet tegn du vil behandle. Jumbled_text bør ikke inneholde flere tegn enn dette tallet (tegn og tall kombinert).
La oss se et eksempel for å gjøre ting klart.
Eksempel: Fjern ikke -numeriske tegn og trekk ut alle tall
Så her har vi litt forvirret tekst. Denne teksten inneholder noen tall og noen ikke -numeriske tegn. Jeg trenger å bli kvitt ikke -numeriske tegn og få numeriske verdier bare i D -kolonnen.
Jeg regner ikke med at det totale antallet tegn i blandet tekst er mer enn 20. Så verdien av NumChars er 20 her. Du kan øke dette tallet hvis du trenger det.
Bruk den generiske formelen ovenfor for å fjerne de ikke -numeriske tegnene.
| =TEKSTJOIN("",EKTE,IFERROR(MIDT(C3,SEKVENS(20),1)+0,"")) |
Og når du trykker på enter -knappen. Du får fjernet alle ikke -numeriske tegn. Dra ned denne formelen for å fjerne tegn fra strengen fra alle cellene i kolonne C3.
Hvordan virker det?
La oss først se hvordan denne formelen løses trinnvis.
| 1-> TEKSTJOIN("",EKTE,IFERROR(MIDT(C3,SEKVENS(20),1)+0,"")) |
| 2-> TEKSTJOIN("",EKTE,IFERROR(MIDT("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEKSTJOIN("",EKTE,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEKSTJOIN("",EKTE,IFERROR({1; 2;#VERDI!;#VERDI!;#VERDI!; 1; 2;#VERDI!; 1; 2; 3;#VERDI!;#VERDI!+0,"")) |
| 5-> TEKSTJOIN("",EKTE,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Som du kan se, begynner formelen å løse fra innsiden. Først er SEQUENCE -funksjonen løst. Siden vi har passert 20. Den returnerer en rekke tall som starter fra 1 til 20.
Denne matrisen serveres til MID -funksjonen som startnummer. Midtfunksjonen går til hver indeks i streng og deler hvert tegn. Du kan se det i trinn 3.
Deretter legger vi til 0 til hvert tegn. I excel hvis du prøver å legge til tall til ikke -numeriske tegn, resulterer det i #VERDI! Feil. Så vi får en rekke tall og #VERDI! Feil. De ikke -numeriske tegnene er borte nå.
Neste IFERROR -funksjon erstatter alle #VALUE -feil med "" (blank). Nå sitter vi igjen med numeriske verdier og mellomrom.
Til slutt serveres denne matrisen til TEXTJOIN -funksjon. TEKSTJOIN -funksjonen kobler dem sammen, og vi får en streng som bare inneholder tall i den.
Forbedring av formelen
Formelen ovenfor brukte et hardt kodet tall for å behandle antall tegn (vi tok tjue). Men du vil kanskje at den skal være dynamisk. I så fall gjettet du det riktig, vi bruker LEN -funksjonen. Det vil ta et eksakt antall tegn for behandling. Så formelen blir.
| = TEXTJOIN ("", TRUE, IFERROR (MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Her vil LEN -funksjonen automatisk oppdage de eksakte talltegnene i den alfanumeriske strengen. Dette vil avlaste byrden for å bestemme maksimalt antall tegn.
Alternativ til SEQUENCE -funksjon
Hvis du ikke vil bruke SEQUENCE -funksjonen, kan du bruke en kombinasjon av ROW og INDIRECT -funksjonen for å generere sekvensielle tall.
| = TEXTJOIN ("", TRUE, IFERROR (MIDD (jumbled_text,RAD(INDIREKT("1:"&LEN(jumbled_text))),1)+0,"")) |
INDIRECT vil konvertere teksten ("1:20") til faktisk område, og deretter vil ROW -funksjonen vise alle radnummer fra 1 til 20. (20 er bare for eksempel. Det kan være et hvilket som helst tall).
Så ja gutta, slik kan du rive av de ikke -numeriske tegnene fra en alfanumerisk streng i excel. Jeg håper jeg var forklarende nok, og denne artikkelen hjalp deg. Hvis du har spørsmål angående dette emnet eller andre excel/VBA -emner. Frem til da fortsetter Excelling.
Del tall og tekst fra streng i Excel 2016 og eldre: Når vi ikke hadde TEXTJOIN -funksjon, brukte vi VENSTRE og HØYRE funksjoner med noen andre funksjoner for å dele numeriske og ikke -numeriske tegn fra en streng.
Pakk ut tekst fra en streng i Excel ved hjelp av Excels VENSTRE og HØYRE -funksjon: For å fjerne tekst i excel fra strengen kan vi bruke excels VENSTRE og HØYRE -funksjon. Disse funksjonene hjelper oss med å kutte strenger dynamisk.
Fjern ledende og etterfølgende mellomrom fra tekst i Excel: Ledende og etterfølgende mellomrom er vanskelig å gjenkjenne visuelt og kan ødelegge dataene dine. Å fjerne disse tegnene fra strengen er en grunnleggende og viktigste oppgave i datarensing. Slik gjør du det enkelt i Excel.
Fjern tegn fra høyre: For å fjerne tegn fra høyre på en streng i Excel, bruker vi VENSTRE -funksjonen. Ja, VENSTRE -funksjonen. LEFT -funksjonen beholder det angitte antallet tegn fra LEFT og fjerner alt fra høyre.
Fjern uønskede tegn i Excel: For å fjerne uønskede tegn fra en streng i Excel bruker vi SUBSTITUTE -funksjonen. SUBSTITUTE -funksjonen erstatter de gitte tegnene med et annet gitt tegn og produserer en ny endret streng.
Slik fjerner du tekst i Excel fra en posisjon i Excel: For å fjerne tekst fra en startposisjon i en streng, bruker vi REPLACE -funksjonen i Excel. Denne funksjonen hjelper oss med å bestemme startposisjonen og antall tegn som skal stripes.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.