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 i spil med nyt legetøj, jeg mener funktioner.
Excel 2019 og 365 introducerer nogle nye funktioner (TEXTJOIN og SEQUENCE), der kan lette opgaven med at fjerne numeriske tegn og kun hente ikke -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,HVIS(FEJL(MIDT(jumbled_text,SEKVENS(NumChars), 1) +0),MIDT(jumbled_text,SEKVENS(NumChars), 1), "")))) |
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 numeriske tegn, og udtræk alle alfabeter og ikke -numeriske tegn.
Så her har vi lidt alfanumerisk tekst. Denne tekst indeholder nogle tal og nogle ikke -numeriske tegn. Jeg skal slippe af med numeriske tegn og kun få alfabeter og andre tegnværdier i kolonnen D.
Jeg forventer ikke, at det samlede antal tegn i blandet tekst er mere end 100. Så værdien af NumChars er 100 her. Du kan øge eller reducere dette antal, hvis du har brug for det.
Anvend ovenstående generiske formel her for at fjerne de numeriske tegn.
| =TEKSTJOIN("",SAND,HVIS(FEJL(MIDT(B3,SEKVENS(100),1)+0),MIDT(B3,SEKVENS(100),1),""))) |
Når du trykker på enter -knappen, fjernes alle de numeriske tegn, og der er kun alfabetiske værdier tilbage. Træk denne formel ned for at fjerne tal fra strengen fra alle cellerne i kolonne B.
Hvordan virker det?
Lad os først se, hvordan denne formel løses trin for trin.
| 1-> TEKSTJOIN("",SAND,HVIS(FEJL(MIDT(B3,SEKVENS(100),1)+0),MIDT(B3,SEKVENS(100),1),""))) |
| 2-> TEKSTJOIN("",SAND,HVIS(FEJL(MIDT("This1 is … site",{1,2,3,… 100},1)+0),MIDT(B3,SEKVENS(100),1),""))) |
| 3-> TEKSTJOIN("",SAND,HVIS(FEJL({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MIDT(B3,SEKVENS(100),1),""))) |
| 4-> TEKSTJOIN("",SAND,HVIS(FEJL({#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!; 1 …; #VÆRDI!}),MIDT(B3,SEKVENS(100),1),""))) |
| 5-> TEKSTJOIN("",SAND,HVIS({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MIDT(B3,SEKVENS(100),1),""))) |
| 6-> TEKSTJOIN("",SAND,{"Det her";""… .;""}) |
| 7-> "Dette er nummer et websted" |
For nem læsning har jeg ikke skrevet hele arrayet. Jeg brugte prikker (…) til at angive lange arrays.
Som du kan se, begynder formlen at løse indefra. I første omgang er SEQUENCE -funktionen løst. Da vi har passeret 100 som antallet af tegn. Det returnerer en række tal, der starter fra 1 til 100.
Denne matrix serveres til MID -funktionen som startnummer. Midtfunktionen går til hvert indeks i streng og opdeler hvert tegn i en matrix. Du kan se det i trin 3. Jeg har ikke vist hele arrayet, da det vil tage for meget plads. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
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. {#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!; 1 …; #VÆRDI!}
Denne matrix serveres til FEJL fungere. Som du ved returnerer funktionen FEJL SAND for fejl og FALSK for ikke -fejlværdier. Derfor får vi en vifte af SAND og FALSK. TRUE for ikke -numeriske tegn og FALSE for tal. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Det samme sker med SANDE sektionserklæringer fra IF (MIDT(B3,SEKVENS(100),1)). Det returnerer en matrix med alle tegn i alfanumerisk streng i B3.
Nu for hver SAND sætning returnerer IF -funktionen det tilsvarende tegn fra arrayet i den sande sektion. For FALSK returnerer IF blank (""). Nu får du et array, der ikke indeholder noget numerisk tegn. {"Det her";""… .;""}.
Endelig betjenes denne matrix af TEKSTJOIN fungere. Denne funktion forbinder givne tekster med hinanden og ignorerer de tomme værdier med en given afgrænser. Derfor får vi en streng, der ikke indeholder numeriske tegn. Dette er webstedet nummer et.
Forbedring af formlen
Ovenstående formel bruger et hårdt kodet nummer til behandling af antallet af tegn (vi tog 100). Men du vil måske have det dynamisk. I så fald gættede du det rigtigt, du kan bruge LEN -funktionen. Det tager det nøjagtige antal tegn til behandling. Så formlen vil være.
| = TEKSTJOIN ("", SAND, HVIS (FEJL (MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text), 1) +0), MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Her vil LEN -funktionen automatisk registrere de nøjagtige taltegn i den alfanumeriske streng. Dette vil befri fra 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, HVIS (FEJL (MIDD (jumbled_text,RÆKKE(INDIREKT("1:"&LEN(NumChars))), 1) +0), MID (jumbled_text,RÆKKE(INDIREKT("1:"&LEN(NumChars))),1),""))) |
INDIRECT konverterer teksten ("1: 100") til det faktiske område, og derefter viser ROW -funktionen alle rækkenumre fra 1 til 100. (100 er bare et eksempel. Det kan være et hvilket som helst 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.
Sådan fjernes ikke -numeriske tegn fra celler i Excel 2019: For at fjerne ikke -numeriske tegn fra en alfanumerisk streng i Excel bruger vi den nye TEXTJOIN -funktion. Strimmel med ikke -numeriske tegn fra en streng kan hjælpe os med at rydde op i vores data for bedre dataanalyse. Så her er, hvordan du gør det
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.