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 med nye leker, jeg mener funksjoner.
Excel 2019 og 365 introduserer noen nye funksjoner (TEXTJOIN og SEQUENCE) som kan lette oppgaven med å fjerne numeriske tegn og bare hente ikke -numeriske verdier i en ny celle. Vi vil bruke formler som kan hjelpe oss med å gjøre det, mer praktisk.
Generisk formel
| =TEKSTJOIN("",EKTE,HVIS(FEIL(MIDT(jumbled_text,SEKVENS(NumChars), 1) +0),MIDT(jumbled_text,SEKVENS(NumChars), 1), "")))) |
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 numeriske tegn og trekk ut alle alfabeter og ikke -numeriske tegn.
Så her har vi litt alfanumerisk tekst. Denne teksten inneholder noen tall og noen ikke -numeriske tegn. Jeg trenger å bli kvitt numeriske tegn og bare få alfabeter og andre tegnverdier, i D -kolonnen.
Jeg forventer ikke at det totale antallet tegn i blandet tekst vil være mer enn 100. Så verdien av NumChars er 100 her. Du kan øke eller redusere dette tallet hvis du trenger det.
Bruk den generiske formelen ovenfor for å fjerne de numeriske tegnene.
| =TEKSTJOIN("",EKTE,HVIS(FEIL(MIDT(B3,SEKVENS(100),1)+0),MIDT(B3,SEKVENS(100),1),""))) |
Når du trykker på enter -knappen, fjerner du alle de numeriske tegnene, og bare alfabetiske verdier gjenstår. Dra ned denne formelen for å fjerne tall fra strengen fra alle cellene i kolonne B.
Hvordan virker det?
La oss først se hvordan denne formelen løses trinnvis.
| 1-> TEKSTJOIN("",EKTE,HVIS(FEIL(MIDT(B3,SEKVENS(100),1)+0),MIDT(B3,SEKVENS(100),1),""))) |
| 2-> TEKSTJOIN("",EKTE,HVIS(FEIL(MIDT("This1 is … site",{1,2,3,… 100},1)+0),MIDT(B3,SEKVENS(100),1),""))) |
| 3-> TEKSTJOIN("",EKTE,HVIS(FEIL({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MIDT(B3,SEKVENS(100),1),""))) |
| 4-> TEKSTJOIN("",EKTE,HVIS(FEIL({#VERDI!;#VERDI!;#VERDI!;#VERDI!; 1…;#VERDI!}),MIDT(B3,SEKVENS(100),1),""))) |
| 5-> TEKSTJOIN("",EKTE,HVIS({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MIDT(B3,SEKVENS(100),1),""))) |
| 6-> TEKSTJOIN("",EKTE,{"T"; "h"; "i"; "s"; ""…. ""}}) |
| 7-> "Dette er nettsted én" |
For enkel lesing har jeg ikke skrevet hele matrisen. Jeg brukte prikker (…) for å indikere lange matriser.
Som du kan se, begynner formelen å løse fra innsiden. Først er SEQUENCE -funksjonen løst. Siden vi har passert 100 som antall tegn. Den returnerer en rekke tall som starter fra 1 til 100.
Denne matrisen serveres til MID -funksjonen som startnummer. Midtfunksjonen går til hver indeks i streng og deler hvert tegn i en matrise. Du kan se det i trinn 3. Jeg har ikke vist hele matrisen, da det vil ta for mye plass. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
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. {#VERDI!;#VERDI!;#VERDI!;#VERDI!; 1…;#VERDI!}
Denne matrisen serveres til FEIL funksjon. Som du vet returnerer FEIL -funksjonen SANN for feil og FALSK for ikke -feilverdier. Derfor får vi en rekke SANN og FALSK. TRUE for ikke -numeriske tegn og FALSE av tall. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Det samme skjer med TRUE section statements of IF (MIDT(B3,SEKVENS(100),1)). Den returnerer en rekke med alle tegn i alfanumerisk streng i B3.
Nå for hver TRUE -setning returnerer IF -funksjonen det tilsvarende tegnet fra matrisen i den sanne delen. For FALSK returnerer IF tomt (""). Nå vil du ha en matrise som ikke inneholder noen numeriske tegn. {"T"; "h"; "i"; "s"; "" ….; ""}.
Til slutt serveres denne matrisen av TEKSTJOIN funksjon. Denne funksjonen forbinder gitte tekster med hverandre, ignorerer de tomme verdiene med en gitt skilletegn. Derfor får vi en streng som ikke inneholder numeriske tegn. Dette er nettsted nummer én.
Forbedring av formelen
Formelen ovenfor bruker et hardt kodet tall for å behandle antall tegn (vi tok 100). Men du vil kanskje at den skal være dynamisk. I så fall gjettet du riktig, du kan bruke LEN -funksjonen. Det vil ta det eksakte antallet tegn for behandling. Så formelen blir.
| = TEKSTJOIN ("", SANN, HVIS (FEIL (MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text), 1) +0), MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Her vil LEN -funksjonen automatisk oppdage de eksakte talltegnene i den alfanumeriske strengen. Dette vil avlaste byrden med å 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.
| = TEKSTJOIN ("", SANN, HVIS (FEIL (MIDD (jumbled_text,RAD(INDIREKT("1:"&LEN(NumChars))), 1) +0), MIDD (jumbled_text,RAD(INDIREKT("1:"&LEN(NumChars))),1),""))) |
INDIRECT vil konvertere teksten ("1: 100") til det faktiske området, og deretter vil ROW -funksjonen vise alle radnummer fra 1 til 100. (100 er bare et 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.
Slik fjerner du ikke -numeriske tegn fra celler i Excel 2019: For å fjerne ikke -numeriske tegn fra en alfanumerisk streng i Excel, bruker vi den nye TEKSTJOIN -funksjonen. Strip av ikke -numeriske tegn fra en streng kan hjelpe oss med å rydde opp i dataene våre for bedre dataanalyse. Så her gjør du det
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.