Ladda ner exempelarbetsboken
Denna handledning kommer visa dig hur du hittar och extraherar ett nummer inifrån en textsträng i Excel och Google Kalkylark.
Hitta och extrahera nummer från sträng
Ibland kan dina data innehålla siffror och text och du vill extrahera de numeriska uppgifterna.
Om nummerdelen finns på höger eller vänster sida av strängen är det relativt enkelt att dela upp nummer och text. Men om siffrorna är inuti strängen, dvs mellan två textsträngar, måste du använda en mycket mer komplicerad formel (visas nedan).
TEXTJOIN - Extrahera nummer i Excel 2016+
I Excel 2016 introducerades TEXTJOIN -funktionen, som kan extrahera siffrorna var som helst i textsträngen. Här är formeln:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Låt oss se hur denna formel fungerar.
Funktionerna RAD, INDIREKT och LEN returnerar en rad nummer som motsvarar varje tecken i din alfanumeriska sträng. I vårt fall har "Monday01Day" 11 tecken, så ROW -funktionen returnerar då matrisen {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), "")) |
Därefter extraherar MID -funktionen varje tecken från textsträngen och skapar en array som innehåller din ursprungliga sträng:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Genom att multiplicera varje värde i matrisen med 1 skapas en matris som innehåller fel Om matrisens tecken var text:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!; 0; 1;#VÄRDE!;#VÄRDE!;#VÄRDE !}), "")) |
Därefter tar IFERROR -funktionen bort felvärdena:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}}) |
Lämnar bara TEXTJOIN -funktionen som sammanfogar de återstående talen.
Observera att formeln ger dig alla de numeriska tecknen tillsammans från din sträng. Om din alfanumeriska sträng till exempel är Monday01Day01, kommer den att ge dig 0101 som resultat.
Extrahera nummer - före Excel 2016
Innan Excel 2016 kan du använda en mycket mer komplicerad metod för att extrahera siffror från text. Du kan använda FIND -funktionen tillsammans med IFERROR- och MIN -funktionerna för att bestämma både den första positionen för nummerdelen och den första positionen för textdelen efter numret. Sedan extraherar vi helt enkelt nummerdelen med MID -funktionen.
| 1 | = MIDD (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (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))) |
Obs! Detta är en Array -formel, du måste ange formeln genom att trycka på CTRL + SKIFT + ENTER, istället för bara ENTER.
Låt oss se steg för steg hur denna formel fungerar.
Hitta näve nummer
Vi kan använda FIND -funktionen för att hitta nummerets startposition.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
För hitta_text -argumentet för FIND -funktionen använder vi arraykonstanten {0,1,2,3,4,5,6,7,8,9}, vilket gör att FIND -funktionen utför en separat sökning för varje värde i matrisen konstant.
Inom_text -argumentet för FIND -funktionen i vårt fall är Monday01Day, där 1 kan hittas på position 8 och 0 på position 7, så vår resultatmatris kommer att vara: {8,#VALUE,#VALUE,#VALUE, #VÄRDE, #VÄRDE, #VÄRDE, #VÄRDE, #VÄRDE, 7}.
Med hjälp av IFERROR -funktionen ersätter vi #VALUE -felen med 999999999. Sedan letar vi helt enkelt efter minimumet inom denna array och får därför platsen för det första numret (7).
Observera att ovanstående formel är en matrisformel, du måste trycka på Ctrl+Skift+Retur för att utlösa den.
Hitta första texttecken efter nummer
På samma sätt som för att lokalisera det första numret i strängen använder vi funktionen HITTA för att avgöra var texten börjar igen efter att numret har använt funktionen start_num väl.
| 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)) |
Denna formel fungerar mycket på samma sätt som den föregående som används för att hitta det första numret, bara vi använder bokstäver i vår matriskonstant och därför orsakar siffror #VÄRDE -fel. Observera att vi börjar söka först efter positionen som fastställts för det första numret (detta är argumentet start_num) och inte från början av strängen.
Glöm inte att trycka på Ctrl+Skift+Retur för att använda ovanstående formel.
Det finns inget kvar än att sätta ihop allt detta.
Extrahera antal från två texter
När vi väl har startpositionen för nummerdelen och början på textdelen efter det, använder vi helt enkelt MID -funktionen för att extrahera önskad nummerdel.
| 1 | = MIDT (B3, C3, D3-C3) |
Annan metod
Utan att förklara det mer i detalj kan du också använda nedanstående komplexa formel för att få numret inifrån en sträng.
| 1 | = SUMPRODUCT (MIDD (0 & B3, STOR (INDEX (ISNUMBER (-MIDD (B3, RAD (INDIRECT ("1:" & LEN (B3))), 1))*RAD (INDIRECT ("1:" & LEN (B3) )), 0), RAD (INDIRECT ("1:" & LEN (B3)))))+1,1)*10^RAD (INDIRECT ("1:" & LEN (B3)))/10) |
Tänk på att den här formeln ovan ger dig 0 när det inte finns något nummer i strängen och att ledande nollor kommer att utelämnas.
Hitta och extrahera nummer från sträng till text i Google Kalkylark
Exemplen som visas ovan fungerar på samma sätt i Google Kalkylark som i Excel.
