Udtræk tal fra en streng i Excel (ved hjælp af formler eller VBA)

Se video - Sådan udtrækkes tal fra tekststreng i Excel (ved hjælp af formel og VBA)

Der er ingen indbygget funktion i Excel til at udtrække tallene fra en streng i en celle (eller omvendt - fjern den numeriske del og udtræk tekstdelen fra en alfanumerisk streng).

Dette kan dog gøres ved hjælp af en cocktail af Excel -funktioner eller en simpel VBA -kode.

Lad mig først vise dig, hvad jeg taler om.

Antag, at du har et datasæt som vist nedenfor, og du vil udtrække tallene fra strengen (som vist nedenfor):

Den metode, du vælger, afhænger også af den version af Excel, du bruger:

• For versioner før Excel 2016 skal du bruge lidt længere formler
• I Excel 2016 kan du bruge den nyligt introducerede TEXTJOIN -funktion
• VBA -metoden kan bruges i alle versioner af Excel

Klik her for at downloade eksempelfilen

Udtræk tal fra streng i Excel (formel til Excel 2016)

Denne formel fungerer kun i Excel 2016, da den bruger den nyligt introducerede TEXTJOIN -funktion.

Denne formel kan også udtrække de tal, der er i begyndelsen, slutningen eller midten af ​​tekststrengen.

Bemærk, at TEXTJOIN -formlen, der er dækket i dette afsnit, giver dig alle de numeriske tegn sammen. For eksempel, hvis teksten er "Prisen på 10 billetter er USD 200", giver den dig 10200 som resultat.

Antag, at du har datasættet som vist nedenfor, og du vil udtrække tallene fra strengene i hver celle:

Nedenfor er formlen, der giver dig numerisk del fra en streng i Excel.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), "")))

Dette er en matrixformel, så du skal bruge 'Ctrl + Skift + Enter‘I stedet for at bruge Enter.

Hvis der ikke er tal i tekststrengen, returnerer denne formel en tom (tom streng).

Hvordan fungerer denne formel?

Lad mig bryde denne formel og prøve at forklare, hvordan den fungerer:

• RÆKKE (INDIREKT (“1:” & LEN (A2))) - denne del af formlen ville give en række tal, der starter fra et. LEN -funktionen i formlen returnerer det samlede antal tegn i strengen. I tilfælde af "Omkostningerne er USD 100", returnerer den 19. Formlerne bliver dermed RÆKKE (INDIREKTE ("1:19"). RÆ -funktionen returnerer derefter en række tal - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
• (MIDD (A2, RÆK (INDIRECT (“1:” & LEN (A2))), 1)*1) - Denne del af formlen ville returnere en matrix med #VÆRDI! fejl eller tal baseret på strengen. Alle teksttegnene i strengen bliver til #VÆRDI! fejl og alle numeriske værdier forbliver som de er. Dette sker, da vi har ganget MID -funktionen med 1.
• IFERROR ((MIDD (A2, RÆK (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Når IFERROR -funktionen bruges, fjernes al #VÆRDI! fejl, og kun tallene ville forblive. Outputtet af denne del ville se sådan ud - {""; ""; ""; "" ""; "" "" ";" ";" ";" "" "" "" "" "; ””; ””; ””; 1; 0; 0}
• = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””)) - TEXTJOIN -funktionen kombinerer nu simpelthen strengtegnene der forbliver (som kun er tallene) og ignorerer den tomme streng.

Pro tip: Hvis du vil kontrollere output fra en del af formlen, skal du markere cellen, trykke på F2 for at komme til redigeringstilstand, vælge den del af formlen, du vil have output til, og trykke på F9. Du vil øjeblikkeligt se resultatet. Og husk derefter enten at trykke på Ctrl + Z eller trykke på Escape -tasten. Tryk IKKE på enter -tasten.

Download eksempelfilen

Du kan også bruge den samme logik til at udtrække tekstdelen fra en alfanumerisk streng. Nedenfor er formlen, der ville få tekstdelen fra strengen:

= TEKSTJOIN ("", SAND, HVIS (FEJL (MIDD (A2, RÆK ("1:" & LEN (A2))), 1)*1), MIDD (A2, RÆKKE (INDIREKT ("1:" & LEN) (A2))), 1), ""))

En mindre ændring i denne formel er, at IF -funktion bruges til at kontrollere, om den matrix, vi får fra MID -funktionen, er fejl eller ej. Hvis det er en fejl, beholder den værdien, ellers erstatter den den med en blank.

Derefter bruges TEXTJOIN til at kombinere alle teksttegnene.

Advarsel: Selvom denne formel fungerer godt, bruger den en flygtig funktion (INDIRECT -funktionen). Dette betyder, at hvis du bruger dette med et stort datasæt, kan det tage noget tid at give dig resultaterne. Det er bedst at oprette en sikkerhedskopi, før du bruger denne formel i Excel.

Udtræk tal fra streng i Excel (til Excel 2013/2010/2007)

Hvis du har Excel 2013. 2010. eller 2007, kan du ikke bruge TEXTJOIN -formlen, så du bliver nødt til at bruge en kompliceret formel for at få dette gjort.

Antag, at du har et datasæt som vist nedenfor, og du vil udtrække alle tallene i strengen i hver celle.

Nedenstående formel får dette gjort:

= HVIS (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," "))))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2))), 1)) * RÆKKE (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2))))+1,1) * 10^ROW (INDIRECT ("$ 1: $" & LEN (A2)))/10), "")

Hvis der ikke er noget tal i tekststrengen, returnerer denne formel blank (tom streng).

Selvom dette er en matrixformel, kan du ikke har brug for at bruge 'Control-Shift-Enter' for at bruge dette. En simpel indtastning fungerer for denne formel.

Tilskrivning til denne formel går til det fantastiske Mr. Excel -forum.

Igen vil denne formel udtrække alle tallene i strengen uanset positionen. For eksempel, hvis teksten er "Prisen på 10 billetter er USD 200", giver den dig 10200 som resultat.

Advarsel: Selvom denne formel fungerer godt, bruger den en flygtig funktion (INDIRECT -funktionen). Det betyder, at hvis du bruger dette med et stort datasæt, kan det tage noget tid at give dig resultaterne. Det er bedst at oprette en sikkerhedskopi, før du bruger denne formel i Excel.

Adskil tekst og tal i Excel ved hjælp af VBA

Hvis adskillelse af tekst og tal (eller udtrækning af tal fra teksten) er noget, du ofte skal, kan du også bruge VBA -metoden.

Alt du skal gøre er at bruge en simpel VBA -kode til at oprette en brugerdefineret brugerdefineret funktion (UDF) i Excel, og derefter bruge denne VBA -formel i stedet for at bruge lange og komplicerede formler.

Lad mig vise dig, hvordan du opretter to formler i VBA - en til at udtrække tal og en til at udtrække tekst fra en streng.

Udtræk tal fra streng i Excel (ved hjælp af VBA)

I denne del vil jeg vise dig, hvordan du opretter den tilpassede funktion for kun at få den numeriske del fra en streng.

Nedenfor er den VBA -kode, vi vil bruge til at oprette denne brugerdefinerede funktion:

Funktion GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Herefter Resultat = Resultat & Mid (CellRef, i, 1) Næste i GetNumeric = Resultatafslutningsfunktion

Her er trinene til at oprette denne funktion og derefter bruge den i regnearket:

• Gå til fanen Udvikler.
• Klik på Visual Basic (Du kan også bruge tastaturgenvejen ALT + F11)
• I VB Editor-backend, der åbnes, skal du højreklikke på et af projektmappeobjekterne.
• Gå til Indsæt og klik på Modul. Dette indsætter modulobjektet til projektmappen.
• I vinduet Modulkode skal du kopiere og indsætte den ovenfor nævnte VBA -kode.
• Luk VB Editor.

Nu vil du kunne bruge GetText -funktionen i regnearket. Da vi har gjort alle de tunge løft i selve koden, er alt du skal gøre at bruge formlen = GetNumeric (A2).

Dette vil øjeblikkeligt kun give dig den numeriske del af strengen.

Bemærk, at da projektmappen nu har VBA -kode i den, skal du gemme den med .xls eller .xlsm -udvidelse.

Download eksempelfilen

Hvis du ofte skal bruge denne formel, kan du også gemme denne i din personlige makro -projektmappe. Dette giver dig mulighed for at bruge denne brugerdefinerede formel i en hvilken som helst af de Excel -projektmapper, du arbejder med.

Udtræk tekst fra en streng i Excel (ved hjælp af VBA)

I denne del vil jeg vise dig, hvordan du opretter den tilpassede funktion for kun at få tekstdelen fra en streng.

Nedenfor er den VBA -kode, vi vil bruge til at oprette denne brugerdefinerede funktion:

Funktion GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) Næste i GetText = Resultatafslutningsfunktion

Her er trinene til at oprette denne funktion og derefter bruge den i regnearket:

• Gå til fanen Udvikler.
• Klik på Visual Basic (Du kan også bruge tastaturgenvejen ALT + F11)
• I VB Editor-backend, der åbnes, skal du højreklikke på et af projektmappeobjekterne.
• Gå til Indsæt og klik på Modul. Dette indsætter modulobjektet til projektmappen.
  • Hvis du allerede har et modul, skal du dobbeltklikke på det (du behøver ikke indsætte et nyt, hvis du allerede har det).
• I vinduet Modulkode skal du kopiere og indsætte den ovenfor nævnte VBA -kode.
• Luk VB Editor.

Nu vil du kunne bruge GetNumeric -funktionen i regnearket. Da vi har gjort alle de tunge løft i selve koden, er alt du skal gøre at bruge formlen = GetText (A2).

Dette vil øjeblikkeligt kun give dig den numeriske del af strengen.

Bemærk, at da projektmappen nu har VBA -kode i den, skal du gemme den med .xls eller .xlsm -udvidelse.

Hvis du ofte skal bruge denne formel, kan du også gemme denne i din personlige makro -projektmappe. Dette giver dig mulighed for at bruge denne brugerdefinerede formel i en hvilken som helst af de Excel -projektmapper, du arbejder med.

Hvis du bruger Excel 2013 eller tidligere versioner og ikke har