Vi har lært, hvordan man fjerner numeriske værdier fra en celle i excel 2016 og ældre. De formler, vi brugte, var lidt komplekse, men nu er Excel 2019 og 365 med i spillet.
Excel 2019 og 365 introducerer nogle nye funktioner, der letter opgaven med at fjerne ikke -numeriske tegn og kun hente numeriske værdier i en ny celle. Vi vil bruge formler, der kan hjælpe os med at gøre det mere bekvemt.
Generisk formel
| =TEKSTJOIN("",SAND,IFERROR(MIDT(jumbled_text,SEKVENS(NumChars), 1) +0, ""))) |
Jumbled_text: Dette er kildeteksten, hvorfra du vil udtrække alle numeriske værdier.
NumChars: Dette er det samlede antal tegn, du vil behandle. Jumbled_text bør ikke have flere tegn end dette tal (tegn og numerisk kombineret).
Lad os se et eksempel for at gøre tingene klare.
Eksempel: Fjern ikke -numeriske tegn, og ekstraher alle tal
Så her har vi noget blandet tekst. Denne tekst indeholder nogle tal og nogle ikke -numeriske tegn. Jeg skal slippe af med ikke -numeriske tegn og kun få numeriske værdier i D -kolonnen.
Jeg forventer ikke, at det samlede antal tegn i blandet tekst er mere end 20. Så værdien af NumChars er 20 her. Du kan øge dette antal, hvis du har brug for det.
Anvend ovenstående generiske formel her for at fjerne de ikke -numeriske tegn.
| =TEKSTJOIN("",SAND,IFERROR(MIDT(C3,SEKVENS(20),1)+0,"")) |
Og når du trykker på enter -knappen. Du får fjernet alle de ikke -numeriske tegn. Træk denne formel ned for at fjerne tegn fra strengen fra alle cellerne i kolonne C3.
Hvordan virker det?
Lad os først se, hvordan denne formel løses trin for trin.
| 1-> TEKSTJOIN("",SAND,IFERROR(MIDT(C3,SEKVENS(20),1)+0,"")) |
| 2-> TEKSTJOIN("",SAND,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("",SAND,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEKSTJOIN("",SAND,IFERROR({1; 2; #VÆRDI!;#VÆRDI!;#VÆRDI!; 1; 2; #VÆRDI!; 1; 2; 3; #VÆRDI!;#VÆRDI!+0,"")) |
| 5-> TEKSTJOIN("",SAND,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Som du kan se, begynder formlen at løse indefra. I første omgang er SEQUENCE -funktionen løst. Da vi har passeret 20. Det returnerer en række tal, der starter fra 1 til 20.
Denne matrix serveres til MID -funktionen som startnummer. Midtfunktionen går til hvert indeks i streng og opdeler hvert tegn. Det kan du se i trin 3.
Dernæst tilføjer vi 0 til hvert tegn. I excel, hvis du prøver at tilføje tal til ikke -numeriske tegn, resulterer det i #VÆRDI! Fejl. Så vi får en række tal og #VÆRDI! Fejl. De ikke -numeriske tegn er væk nu.
Næste IFERROR -funktion erstatter alle #VALUE -fejl med "" (blank). Nu står vi tilbage med numeriske værdier og mellemrum.
Endelig serveres dette array til TEXTJOIN -funktion. Funktionen TEKSTJOIN sammenkæder dem, og vi får en streng, der kun indeholder tal i den.
Forbedring af formlen
Ovenstående formel brugte et hårdt kodet tal til behandling af antallet af tegn (vi tog tyve). Men du vil måske have det dynamisk. I så fald gættede du det rigtigt, vi vil bruge LEN -funktionen. Det tager et nøjagtigt antal tegn til behandling. Så formlen vil være.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Her vil LEN -funktionen automatisk registrere de nøjagtige taltegn i den alfanumeriske streng. Dette vil lette byrden ved at bestemme det maksimale antal tegn.
Alternativ til SEQUENCE -funktion
Hvis du ikke ønsker at bruge SEQUENCE -funktionen, kan du bruge en kombination af ROW og INDIRECT -funktionen til at generere sekventielle tal.
| = TEKSTJOIN ("", SAND, HVISFEJL (MIDD (blandet tekst,RÆKKE(INDIREKT("1:"&LEN(jumbled_text))),1)+0,"")) |
INDIRECT konverterer teksten ("1:20") til det faktiske område, og derefter viser ROW -funktionen alle rækkenumre fra 1 til 20. (20 er for eksempel. Det kan være et vilkårligt tal).
Så ja fyre, sådan kan du rive de ikke -numeriske tegn fra en alfanumerisk streng i excel. Jeg håber, jeg var forklarende nok, og denne artikel hjalp dig. Hvis du har spørgsmål vedrørende dette emne eller andre excel/VBA -emner. Indtil da fortsæt med at udmærke sig.
Opdel tal og tekst fra streng i Excel 2016 og ældre: Da vi ikke havde TEXTJOIN -funktion, brugte vi VENSTRE og HØJRE funktioner med nogle andre funktioner til at opdele numeriske og ikke -numeriske tegn fra en streng.
Udtræk tekst fra en streng i Excel ved hjælp af Excels VENSTRE og HØJRE funktion: For at fjerne tekst i excel fra streng kan vi bruge excels VENSTRE og HØJRE -funktion. Disse funktioner hjælper os med at hugge strenge dynamisk.
Fjern ledende og efterfølgende mellemrum fra tekst i Excel: Ledende og efterfølgende mellemrum er svært at genkende visuelt og kan ødelægge dine data. Fjernelse af disse tegn fra strengen er en grundlæggende og vigtigste opgave i datarensning. Sådan kan du nemt gøre det i Excel.
Fjern tegn fra højre: For at fjerne tegn fra højre for en streng i Excel bruger vi funktionen VENSTRE. Ja, VENSTRE -funktionen. LEFT -funktionen bevarer det givne antal tegn fra LEFT og fjerner alt fra højre side.
Fjern uønskede tegn i Excel: For at fjerne uønskede tegn fra en streng i Excel bruger vi SUBSTITUTE -funktionen. SUBSTITUTE -funktionen erstatter de givne tegn med et andet givet tegn og producerer en ny ændret streng.
Sådan fjernes tekst i Excel fra en position i Excel: For at fjerne tekst fra en startposition i en streng bruger vi funktionen REPLACE i Excel. Denne funktion hjælper os med at bestemme startpositionen og antallet af tegn, der skal strippes.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges Excel COUNTIF -funktion| Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.