Det er ingen innebygd funksjon i Excel for å trekke ut tall fra en streng i en celle (og omvendt - fjern den numeriske delen og trekk ut tekstdelen fra en alfanumerisk streng).
Dette kan imidlertid gjøres ved hjelp av en Excel-funksjon eller en enkel VBA-kode.
La meg først fortelle deg hva jeg snakker om.
Anta at du har et datasett som nedenfor og du vil trekke ut tall fra strenger (som nedenfor):
Metoden du velger avhenger også av hvilken versjon av Excel du bruker:
- For versjoner av Excel før 2016, må du bruke en litt lengre formel
- For Excel 2016 kan du bruke den nylig introduserte TEXTJOIN-funksjonen
- VBA-metode tilgjengelig for alle versjoner av Excel
Innhold
Trekk ut tall fra strenger i Excel (Excel 2016-formler)
Denne formelen fungerer bare i Excel 2016 fordi den bruker den nylig introduserte TEXTJOIN-funksjonen.
I tillegg kan denne formelen trekke ut tall på begynnelsen, slutten eller midten av en tekststreng.
Merk at TEXTJOIN-formelen beskrevet i denne delen setter alle numeriske tegn sammen.For eksempel, hvis teksten er "10 billetter er $200", er resultatet 10200.
Anta at du har et datasett som følgende, og du vil trekke ut tall fra strenger i hver celle:
Formelen nedenfor vil gi deg den numeriske delen av en streng i Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Dette er en matriseformel, så du må bruke " Ctrl + Shift + Enter " i stedet for å bruke Enter.
Denne formelen vil returnere tom (tom streng) hvis det ikke er tall i tekststrengen.
Hvordan fungerer denne formelen?
La meg bryte ned denne formelen og prøve å forklare hvordan den fungerer:
- ROW(INDIRECT(“1:”&LEN(A2))) – Denne delen av formelen vil gi en serie tall som starter fra én.LEN-funksjonen i formelen returnerer det totale antallet tegn i strengen.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MIDDEL(A2,RAD(INDIREKTE("1:"&LEN(A2))),1)*1) - Denne delen av formelen vil returnere en matrise med #VERDIER!Strengbaserte feil eller tall.Alle teksttegnene i strengen blir #VERDI!Feil og alle verdier forblir som de er.Dette skjer når vi multipliserer MID-funksjonen med 1.
- IFERROR((MID(A2,RAD(INDIREKTE(“1:”&LEN(A2))),1)*1),””) – Når IFERROR-funksjonen brukes, fjernes alle #VERDIER!Feil, bare tall blir igjen.Utgangen av denne delen ser slik ut - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – TEXTJOIN-funksjonen kombinerer nå ganske enkelt strengtegn Fortsatt der (bare tall) og ignorerer tomme strenger.
Proff-tips:Hvis du vil sjekke utdataene til en delformel, velg cellen, trykk F2 for å gå inn i redigeringsmodus, velg delen av formelen du vil skrive ut, og trykk F9.Du vil se resultatene umiddelbart.Så husk å trykke Control + Z eller trykk Escape.Ikke trykk enter.
Du kan også bruke den samme logikken til å trekke ut deler av teksten fra alfanumeriske strenger.Her er formelen for å hente tekstdelen fra strengen:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
En liten endring i denne formelen er at IF-funksjonen brukes til å sjekke matrisen vi får fra MID-funksjonen for feil.Hvis det er en feil, behold verdien, ellers erstatt den med blank.
Bruk deretter TEXTJOIN for å kombinere alle teksttegn.
注意: Selv om denne formelen er gyldig, bruker den en flyktig funksjon (den INDIREKTE funksjonen).Dette betyr at hvis du bruker det med et stort datasett, kan det ta litt tid å gi deg resultater.Det er best å lage en sikkerhetskopi før du bruker denne formelen i Excel.
Trekk ut tall fra strenger i Excel (for Excel 2013/2010/2007)
Hvis du har Excel 2013. 2010. eller 2007, kan du ikke bruke TEXTJOIN-formler, så du må bruke komplekse formler for å gjøre dette.
Anta at du har et datasett som nedenfor og du vil trekke ut alle tallene i strenger i hver celle.
Formelen nedenfor vil gjøre dette:
=IF(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))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Denne formelen vil returnere tom (tom streng) hvis det ikke er tall i tekststrengen.
Selv om dette er en matriseformel, duIngen behovBruk "Control-Shift-Enter" for å bruke den.En enkel inngang fungerer for denne formelen.
Kreditt for denne formelen går til det fantastiskeMr. Excel-forum.
Igjen vil denne formelen trekke ut alle tallene i strengen uavhengig av posisjon.For eksempel, hvis teksten er "10 billetter er $200", er resultatet 10200.
注意: Selv om denne formelen er gyldig, bruker den en flyktig funksjon (den INDIREKTE funksjonen).Dette betyr at hvis du bruker det med et stort datasett, kan det ta litt tid å gi deg resultater.Det er best å lage en sikkerhetskopi før du bruker denne formelen i Excel.
Skill tekst og tall i Excel med VBA
Hvis du ofte trenger å skille tekst og tall (eller trekke ut tall fra tekst), kan du også bruke VBA-metoden.
Alt du trenger å gjøre er å lage en tilpasset brukerdefinert funksjon (UDF) i Excel ved å bruke enkel VBA-kode, og deretter bruke den VBA-formelen i stedet for en lang og kompleks formel.
La meg vise deg hvordan du lager to formler i VBA - en for å trekke ut tall og en for å trekke ut tekst fra strenger.
Trekk ut tall fra strenger i Excel (ved hjelp av VBA)
I denne delen skal jeg vise deg hvordan du lager en egendefinert funksjon for å få bare den numeriske delen fra en streng.
Her er VBA-koden vi skal bruke for å lage denne tilpassede 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 = Resultat sluttfunksjon
Her er trinnene for å lage denne funksjonen og deretter bruke den i et regneark:
Du vil nå kunne bruke GetText-funksjonen på regnearket.Siden vi allerede 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 den numeriske delen av strengen.
Merk at siden arbeidsboken nå inneholder VBA-kode, må du lagre den med en .xls- eller .xlsm-utvidelse.
Hvis du må bruke denne formelen ofte, kan du også lagre den i din personlige makroarbeidsbok.Dette lar deg bruke denne egendefinerte formelen i enhver Excel-arbeidsbok du bruker.
Trekk ut tekst fra streng i Excel (ved hjelp av VBA)
I denne delen skal jeg vise deg hvordan du lager en egendefinert funksjon for å få bare tekstdelen fra en streng.
Her er VBA-koden vi skal bruke for å lage denne tilpassede funksjonen:
Funksjon GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 Til StringLength Hvis ikke (IsNumeric(Mid(CellRef, i, 1))) Deretter Resultat = Resultat & Midt(CellRef, i, 1) ) Neste i GetText = Resultat sluttfunksjon
Her er trinnene for å lage denne funksjonen og deretter bruke den i et regneark:
Du vil nå 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 den numeriske delen av strengen.
Merk at siden arbeidsboken nå inneholder VBA-kode, må du lagre den med en .xls- eller .xlsm-utvidelse.
Hvis du må bruke denne formelen ofte, kan du også lagre den i din personlige makroarbeidsbok.Dette lar deg bruke denne egendefinerte formelen i enhver Excel-arbeidsbok du bruker.
