✅ Pakk ut tall fra en streng i Excel (ved hjelp av formler eller VBA)

Innholdsfortegnelse

Pakk ut tall fra streng i Excel (Formel for Excel 2016)
Pakk ut tall fra streng i Excel (for Excel 2013/2010/2007)
Skill tekst og tall i Excel ved hjelp av VBA

Se video - Slik trekker du ut tall fra tekststreng i Excel (ved hjelp av formel og VBA)

Det er ingen innebygd funksjon i Excel for å trekke ut tallene fra en streng i en celle (eller omvendt - fjern den numeriske delen og trekk ut tekstdelen fra en alfanumerisk streng).

Dette kan imidlertid gjøres ved å bruke en cocktail av Excel -funksjoner eller en enkel VBA -kode.

La meg først vise deg hva jeg snakker om.

Anta at du har et datasett som vist nedenfor, og du vil trekke ut tallene fra strengen (som vist nedenfor):

Metoden du velger vil også avhenge av hvilken versjon av Excel du bruker:

For versjoner før Excel 2016 må du bruke litt lengre formler
For Excel 2016 kan du bruke den nylig introduserte TEXTJOIN -funksjonen
VBA -metoden kan brukes i alle versjoner av Excel

Klikk her for å laste ned eksempelfilen

Pakk ut tall fra streng i Excel (Formel for Excel 2016)

Denne formelen fungerer bare i Excel 2016 da den bruker den nylig introduserte TEXTJOIN -funksjonen.

Denne formelen kan også trekke ut tallene som er i begynnelsen, slutten eller midten av tekststrengen.

Vær oppmerksom på at TEXTJOIN -formelen som dekkes i denne delen vil gi deg alle de numeriske tegnene sammen. For eksempel, hvis teksten er "Prisen på 10 billetter er USD 200", vil den gi deg 10200 som resultat.

Anta at du har datasettet som vist nedenfor, og du vil trekke ut tallene fra strengene i hver celle:

Nedenfor er formelen som gir deg numerisk del fra en streng i Excel.

= TEXTJOIN ("", TRUE, IFERROR ((MIDD (A2, RAD (INDIRECT ("1:" & LEN (A2))), 1)*1), ""))

Dette er en matriseformel, så du må bruke 'Ctrl + Shift + Enter"I stedet for å bruke Enter.

Hvis det ikke er tall i tekststrengen, vil denne formelen returnere en tom (tom streng).

Hvordan fungerer denne formelen?

La meg bryte denne formelen og prøve å forklare hvordan den fungerer:

RAD (INDIRECT (“1:” & LEN (A2))) - denne delen av formelen vil gi en rekke tall som starter fra ett. LEN -funksjonen i formelen returnerer det totale antallet tegn i strengen. I tilfellet "Kostnaden er USD 100", vil den returnere 19. Formlene vil dermed bli RAD (INDIREKTE ("1:19"). RAD -funksjonen vil da returnere en rekke tall - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MIDD (A2, RAD (INDIRECT (“1:” & LEN (A2))), 1)*1) - Denne delen av formelen vil returnere en rekke #VALUE! feil eller tall basert på strengen. Alle teksttegnene i strengen blir #VERDI! feil og alle numeriske verdier forblir som de er. Dette skjer når vi har multiplisert MID -funksjonen med 1.
IFERROR ((MIDD (A2, RAD (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Når IFERROR -funksjonen brukes, ville den fjerne #VERDI! feil, og bare tallene vil forbli. Produksjonen til denne delen vil se slik ut - {""; ""; ""; "" ""; "" "" "" ""; ""; "" "" "" "" ""; ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MIDD (A2, RAD (INDIRECT (“1:” & LEN (A2))), 1)*1), ””)) - TEKSTJOIN -funksjonen kombinerer nå enkelt strengtegnene som gjenstår (som bare er tallene) og ignorerer den tomme strengen.

Profftips: Hvis du vil kontrollere utdataene til en del av formelen, velger du cellen, trykker på F2 for å komme til redigeringsmodus, velger den delen av formelen du vil ha utdata for, og trykker på F9. Du vil umiddelbart se resultatet. Og husk å enten trykke Ctrl + Z eller trykke Escape -tasten. IKKE trykk enter -tasten.

Last ned eksempelfilen

Du kan også bruke den samme logikken til å trekke ut tekstdelen fra en alfanumerisk streng. Nedenfor er formelen som vil få tekstdelen fra strengen:

= TEKSTJOIN ("", SANN, HVIS (FEIL (MIDD (A2, RAD ("1:" og LEN (A2))), 1)*1), MIDD (A2, RAD (INDIREKT ("1:" og LENG) (A2))), 1), ""))

En mindre endring i denne formelen er at IF -funksjonen brukes til å kontrollere om matrisen vi får fra MID -funksjonen er feil eller ikke. Hvis det er en feil, beholder den verdien ellers erstatter den den med en tom.

Deretter brukes TEXTJOIN til å kombinere alle teksttegnene.

Forsiktighet: Selv om denne formelen fungerer bra, bruker den en flyktig funksjon (INDIRECT -funksjonen). Dette betyr at hvis du bruker dette med et stort datasett, kan det ta litt tid å gi deg resultatene. Det er best å lage en sikkerhetskopi før du bruker denne formelen i Excel.

Pakk ut tall fra streng i Excel (for Excel 2013/2010/2007)

Hvis du har Excel 2013. 2010. eller 2007, kan du ikke bruke TEXTJOIN -formelen, så du må bruke en komplisert formel for å få dette gjort.

Anta at du har et datasett som vist nedenfor, og du vil trekke ut alle tallene i strengen i hver celle.

Formelen nedenfor vil gjøre dette:

= HVIS (SUM (LENG (A2) -LEN (ERSTATNING (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," "))))> 0, SUMPRODUCT (MIDD (0 & A2, STOR (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2))), 1)) * RAD (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2))))+1,1) * 10^RAD (INDIRECT ("$ 1: $" og LEN (A2)))/10), "")

Hvis det ikke er noe tall i tekststrengen, returnerer denne formelen blank (tom streng).

Selv om dette er en matriseformel, er du trenger ikke å bruke 'Control-Shift-Enter' for å bruke dette. En enkel enter fungerer for denne formelen.

Kreditt til denne formelen går til det fantastiske Mr. Excel -forumet.

Igjen vil denne formelen trekke ut alle tallene i strengen uansett posisjon. For eksempel hvis teksten er "Prisen på 10 billetter er USD 200", vil den gi deg 10200 som resultat.

Skill tekst og tall i Excel ved hjelp av VBA

Hvis du må skille tekst og tall (eller trekke tall fra teksten) ofte, kan du også bruke VBA -metoden.

Alt du trenger å gjøre er å bruke en enkel VBA -kode for å lage en tilpasset brukerdefinert funksjon (UDF) i Excel, og deretter bruke den VBA -formelen i stedet for å bruke lange og kompliserte formler.

La meg vise deg hvordan du lager to formler i VBA - en for å trekke ut tall og en for å trekke ut tekst fra en streng.

Pakk ut tall fra streng i Excel (ved hjelp av VBA)

I denne delen vil jeg vise deg hvordan du lager den egendefinerte funksjonen for å få bare den numeriske delen fra en streng.

Nedenfor er VBA -koden vi vil bruke til å lage denne egendefinerte funksjonen:

Funksjon GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Resultatsluttfunksjon

Her er trinnene for å lage denne funksjonen og deretter bruke den i regnearket:

Gå til kategorien Utvikler.
Klikk på Visual Basic (Du kan også bruke hurtigtasten ALT + F11)
I VB Editor-backend som åpnes, høyreklikker du på et av arbeidsbokobjektene.
Gå til Sett inn og klikk på Modul. Dette setter inn modulobjektet for arbeidsboken.
I modulkodevinduet, kopier og lim inn VBA -koden som er nevnt ovenfor.
Lukk VB Editor.

Nå vil du kunne bruke GetText -funksjonen i regnearket. Siden vi har gjort alle de tunge løftene i selve koden, er alt du trenger å gjøre å bruke formelen = GetNumeric (A2).

Dette vil umiddelbart gi deg bare den numeriske delen av strengen.

Vær oppmerksom på at siden arbeidsboken nå har VBA -kode i den, må du lagre den med .xls eller .xlsm forlengelse.

Last ned eksempelfilen

Hvis du må bruke denne formelen ofte, kan du også lagre denne i din personlige makro arbeidsbok. Dette lar deg bruke denne egendefinerte formelen i hvilken som helst av Excel -arbeidsbøkene du jobber med.

Pakk ut tekst fra en streng i Excel (ved hjelp av VBA)

I denne delen vil jeg vise deg hvordan du lager den egendefinerte funksjonen for å få bare tekstdelen fra en streng.

Nedenfor er VBA -koden vi vil bruke til å lage denne egendefinerte funksjonen:

Funksjon 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 ) Neste i GetText = Resultatsluttfunksjon

Her er trinnene for å lage denne funksjonen og deretter bruke den i regnearket:

Gå til kategorien Utvikler.
Klikk på Visual Basic (Du kan også bruke hurtigtasten ALT + F11)
I VB Editor-backend som åpnes, høyreklikker du på et av arbeidsbokobjektene.
Gå til Sett inn og klikk på Modul. Dette setter inn modulobjektet for arbeidsboken.
- Hvis du allerede har en modul, dobbeltklikker du på den (du trenger ikke sette inn en ny hvis du allerede har den).
I modulkodevinduet, kopier og lim inn VBA -koden som er nevnt ovenfor.
Lukk VB Editor.

Nå vil du kunne bruke GetNumeric -funksjonen i regnearket. Siden vi har gjort alle de tunge løftene i selve koden, er alt du trenger å gjøre å bruke formelen = GetText (A2).

Dette vil umiddelbart gi deg bare den numeriske delen av strengen.

Vær oppmerksom på at siden arbeidsboken nå har VBA -kode i den, må du lagre den med .xls eller .xlsm forlengelse.

Hvis du må bruke denne formelen ofte, kan du også lagre denne i din personlige makro arbeidsbok. Dette lar deg bruke denne egendefinerte formelen i alle Excel -arbeidsbøkene du jobber med.

Hvis du bruker Excel 2013 eller tidligere versjoner og ikke har det