Download eksemplet på projektmappe
Denne vejledning vil demonstrere dig, hvordan du finder og udtrækker et tal inde fra en tekststreng i Excel og Google Sheets.
Find og uddrag nummer fra streng
Nogle gange kan dine data indeholde tal og tekst, og du vil udtrække de numeriske data.
Hvis nummerdelen er på højre eller venstre side af strengen, er det relativt let at opdele nummeret og teksten. Men hvis tallene er inde i strengen, dvs. mellem to tekststrenge, skal du bruge en meget mere kompliceret formel (vist nedenfor).
TEXTJOIN - Uddrag tal i Excel 2016+
I Excel 2016 blev TEXTJOIN -funktionen introduceret, som kan udtrække tallene hvor som helst i tekststrengen. Her er formlen:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MIDD (B3, RÆK ("1:" & LEN (B3))), 1)*1), "")) |
Lad os se, hvordan denne formel fungerer.
Funktionerne Række, INDIREKTE og LEN returnerer en række tal, der svarer til hvert tegn i din alfanumeriske streng. I vores tilfælde har "Monday01Day" 11 tegn, så ROW -funktionen returnerer derefter arrayet {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Dernæst udtrækker MID -funktionen hvert tegn fra tekststrengen og skaber et array, der indeholder din originale streng:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Multiplicering af hver værdi i arrayet med 1 vil skabe et array, der indeholder fejl, hvis array -tegnet var tekst:
| 1 | = TEKSTJOIN ("", SAND, HVISFEJL (({#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!; 0; 1;#VÆRDI!;#VÆRDI! !}), "")) |
Derefter fjerner IFERROR -funktionen fejlværdierne:
| 1 | = TEKSTJOIN ("", SAND, {""; ""; ""; ""; ""; ""; 0; 1; ""; "" "" "}) |
Efterlader kun TEXTJOIN -funktionen, der forbinder de resterende tal sammen.
Bemærk, at formlen giver dig alle de numeriske tegn sammen fra din streng. For eksempel, hvis din alfanumeriske streng er Monday01Day01, giver den dig 0101 som resultat.
Uddrag tal - før Excel 2016
Før Excel 2016 kunne du bruge en meget mere kompliceret metode til at udtrække tal fra tekst. Du kan bruge FIND -funktionen sammen med IFERROR- og MIN -funktionerne til at bestemme både nummerets første position og tekstdelens første position efter nummeret. Så udtrækker vi ganske enkelt nummerdelen med MID -funktionen.
| 1 | = MIDD (B3, MIN (HVISFEJL (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (HVISFEJL (SØG ({"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))) |
Bemærk: Dette er en Array -formel, du skal indtaste formlen ved at trykke på CTRL + SKIFT + ENTER, i stedet for bare ENTER.
Lad os se trin for trin, hvordan denne formel fungerer.
Find knytnæve nummer
Vi kan bruge FIND -funktionen til at finde nummerets startposition.
| 1 | = MIN (HVISFEJL (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
For find_text -argumentet i FIND -funktionen bruger vi matrixkonstanten {0,1,2,3,4,5,6,7,8,9}, som gør FIND -funktionen til at udføre en separat søgning efter hver værdi i array konstant.
Inden_tekstargumentet for FIND -funktionen i vores tilfælde er Monday01Day, hvor 1 kan findes på position 8 og 0 på position 7, så vores resultat array vil være: {8,#VALUE,#VALUE,#VALUE, #VÆRDI, #VÆRDI, #VÆRDI, #VÆRDI, #VÆRDI, 7}.
Ved hjælp af IFERROR -funktionen erstatter vi #VÆRDI -fejlene med 999999999. Så leder vi simpelthen efter minimumet inden for dette array og får derfor stedet for det første tal (7).
Bemærk, at ovenstående formel er en matrixformel, du skal trykke på Ctrl+Shift+Enter for at udløse den.
Find første teksttegn efter nummer
På samme måde som for at lokalisere det første tal i strengen, bruger vi funktionen FIND til at bestemme, hvor teksten begynder igen, efter at nummeret også har gjort godt brug af start_num -argumentet for funktionen.
| 1 | = MIN (HVISFEJL (FIND ({"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 formel fungerer meget på samme måde som den forrige, der blev brugt til at finde det første tal, kun vi bruger bogstaver i vores matrixkonstant, og derfor forårsager tal #VÆRDI -fejl. Bemærk, at vi først begynder at søge efter den position, der er bestemt til det første tal (dette er argumentet start_num) og ikke fra begyndelsen af strengen.
Glem ikke at trykke på Ctrl+Shift+Enter for at bruge ovenstående formel.
Der er ikke noget tilbage end at sammensætte det hele.
Uddrag nummer fra to tekster
Når vi har nummerpladens startposition og begyndelsen på tekstdelen efter det, bruger vi simpelthen MID -funktionen til at udtrække den ønskede nummerdel.
| 1 | = MIDT (B3, C3, D3-C3) |
Anden metode
Uden at forklare det mere detaljeret, kan du også bruge nedenstående komplekse formel til at hente tallet inde fra en streng.
| 1 | = SUMPRODUCT (MIDD (0 & B3, STOR (INDEX (ISNUMBER (-MIDD (B3, RÆK ("1:" & LEN (B3))), 1))*RÆKKE (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3)))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3))))/10) |
Vær opmærksom på, at denne formel ovenfor giver dig 0, når der ikke findes et tal i strengen, og at ledende nuller udelades.
Find og uddrag nummer fra streng til tekst i Google Sheets
Eksemplerne vist ovenfor fungerer på samme måde i Google Sheets som i Excel.
