Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere deg hvordan du finner og trekker ut et tall fra en tekststreng i Excel og Google Sheets.
Finn og trekk ut tall fra streng
Noen ganger kan dataene inneholde tall og tekst, og du vil trekke ut de numeriske dataene.
Hvis talldelen er på høyre eller venstre side av strengen, er det relativt enkelt å dele nummeret og teksten. Men hvis tallene er inne i strengen, dvs. mellom to tekststrenger, må du bruke en mye mer komplisert formel (vist nedenfor).
TEXTJOIN - Pakk ut tall i Excel 2016+
I Excel 2016 ble TEXTJOIN -funksjonen introdusert, som kan trekke ut tallene hvor som helst i tekststrengen. Her er formelen:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MIDD (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
La oss se hvordan denne formelen fungerer.
Funksjonene RAD, INDIREKT og LEN returnerer en rekke tall som tilsvarer hvert tegn i den alfanumeriske strengen. I vårt tilfelle har “Monday01Day” 11 tegn, så ROW -funksjonen returnerer deretter matrisen {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MIDD (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Deretter trekker MID -funksjonen ut hvert tegn fra tekststrengen, og oppretter en matrise som inneholder den originale strengen:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Multiplisering av hver verdi i matrisen med 1 vil opprette en matrise som inneholder feil Hvis matrisetegnet var tekst:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VERDI!;#VERDI!;#VERDI!;#VERDI!;#VERDI!;#VERDI!; 0; 1;#VERDI!;#VERDI!;#VERDI) !}), "")) |
Deretter fjerner IFERROR -funksjonen feilverdiene:
| 1 | = TEKSTJOIN ("", SANN, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}}) |
Etterlater bare TEXTJOIN -funksjonen som kobler de resterende tallene sammen.
Vær oppmerksom på at formelen vil gi deg alle de numeriske tegnene sammen fra strengen din. For eksempel, hvis den alfanumeriske strengen din er Monday01Day01, vil den gi deg 0101 som et resultat.
Trekk ut tall - før Excel 2016
Før Excel 2016 kan du bruke en mye mer komplisert metode for å trekke ut tall fra tekst. Du kan bruke FIND -funksjonen sammen med IFERROR og MIN -funksjonene for å bestemme både den første plasseringen av nummerdelen og den første plasseringen av tekstdelen etter nummeret. Deretter trekker vi ganske enkelt ut talldelen med MID -funksjonen.
| 1 | = MIDT (B3, MIN (HVISFEIL (FINN ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (HVISFEIL (SØK ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Merk: Dette er en Array -formel, du må angi formelen ved å trykke CTRL + SKIFT + ENTER, i stedet for bare ENTER.
La oss se trinn for trinn hvordan denne formelen fungerer.
Finn knyttneve nummer
Vi kan bruke FIND -funksjonen til å finne startposisjonen til nummeret.
| 1 | = MIN (IFERROR (FINN ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
For finn_tekst -argumentet for FIND -funksjonen bruker vi matrisekonstanten {0,1,2,3,4,5,6,7,8,9}, som gjør FIND -funksjonen til å utføre et separat søk for hver verdi i matrisekonstanten.
Argumentet inside_text for FIND -funksjonen i vårt tilfelle er Monday01Day, hvor 1 kan bli funnet på posisjon 8 og 0 på posisjon 7, så resultatmateriellet vårt vil være: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Ved å bruke IFERROR -funksjonen erstatter vi #VALUE -feilene med 999999999. Så ser vi ganske enkelt etter minimumet i denne matrisen og får derfor stedet til det første tallet (7).
Vær oppmerksom på at formelen ovenfor er en matriseformel, du må trykke Ctrl+Shift+Enter for å utløse den.
Finn første teksttegn etter tall
På samme måte som for å finne det første tallet i strengen, bruker vi FIND -funksjonen til å bestemme hvor teksten begynner igjen etter at tallet også har brukt godt_start -nummer -argumentet til funksjonen.
| 1 | = MIN (IFERROR (FINN ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Denne formelen fungerer veldig likt den forrige som ble brukt for å finne det første tallet, bare vi bruker bokstaver i matrisekonstanten vår og derfor forårsaker tall #VALUE feil. Vær oppmerksom på at vi begynner å søke først etter posisjonen som er bestemt for det første tallet (dette vil være start_num -argumentet) og ikke fra begynnelsen av strengen.
Ikke glem å trykke Ctrl+Shift+Enter for å bruke formelen ovenfor.
Det er ingenting igjen enn å sette alt sammen.
Trekk ut tall fra to tekster
Når vi har startposisjonen til talldelen og begynnelsen på tekstdelen etter det, bruker vi ganske enkelt MID -funksjonen for å trekke ut ønsket nummerdel.
| 1 | = MIDT (B3, C3, D3-C3) |
Annen metode
Uten å forklare det mer detaljert, kan du også bruke den komplekse formelen nedenfor for å få tallet fra innsiden av en streng.
| 1 | = SUMPRODUCT (MIDD (0 & B3, STOR (INDEKS (ISNUMBER (-MIDD (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1))*RAD (INDIRECT ("1:" & LEN (B3) )), 0), RAD (INDIRECT ("1:" & LEN (B3))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3))))/10) |
Vær oppmerksom på at denne formelen ovenfor gir deg 0 når det ikke finnes noe tall i strengen og at ledende nuller blir utelatt.
Finn og trekk ut tall fra streng til tekst i Google Regneark
Eksemplene vist ovenfor fungerer på samme måte i Google Regneark som i Excel.
