Extrahera siffror från en sträng i Excel (med hjälp av formler eller VBA)

Titta på video - Hur man extraherar siffror från textsträng i Excel (med formel och VBA)

Det finns ingen inbyggd funktion i Excel för att extrahera siffrorna från en sträng i en cell (eller vice versa - ta bort den numeriska delen och extrahera textdelen från en alfanumerisk sträng).

Detta kan dock göras med en cocktail av Excel -funktioner eller någon enkel VBA -kod.

Låt mig först visa dig vad jag pratar om.

Antag att du har en datamängd som visas nedan och du vill extrahera siffrorna från strängen (som visas nedan):

Vilken metod du väljer beror också på vilken Excel -version du använder:

• För versioner före Excel 2016 måste du använda något längre formler
• För Excel 2016 kan du använda den nyligen introducerade TEXTJOIN -funktionen
• VBA -metoden kan användas i alla versioner av Excel

Klicka här för att ladda ner exempelfilen

Extrahera nummer från sträng i Excel (Formel för Excel 2016)

Denna formel fungerar bara i Excel 2016 eftersom den använder den nyligen introducerade TEXTJOIN -funktionen.

Denna formel kan också extrahera siffrorna i början, slutet eller mitten av textsträngen.

Observera att TEXTJOIN -formeln som täcks i detta avsnitt ger dig alla numeriska tecken tillsammans. Om texten till exempel är ”Priset på 10 biljetter är 200 USD”, kommer det att ge dig 10200 som resultat.

Anta att du har datauppsättningen enligt nedan och du vill extrahera siffrorna från strängarna i varje cell:

Nedan finns formeln som ger dig numerisk del från en sträng i Excel.

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

Detta är en matrisformel, så du måste använda 'Ctrl + Skift + Enter"Istället för att använda Enter.

Om det inte finns några nummer i textsträngen, skulle denna formel returnera ett tomt (tom sträng).

Hur fungerar denna formel?

Låt mig bryta denna formel och försöka förklara hur det fungerar:

• RAD (INDIRECT (“1:” & LEN (A2))) - denna del av formeln skulle ge en rad nummer som börjar med ett. LEN -funktionen i formeln returnerar det totala antalet tecken i strängen. I fallet "Kostnaden är USD 100", kommer den att returnera 19. Formlerna skulle alltså bli RAD (INDIRECT ("1:19"). RAD -funktionen returnerar sedan en rad nummer - {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) - Denna del av formeln skulle returnera en uppsättning #VÄRDE! fel eller siffror baserade på strängen. Alla texttecken i strängen blir #VÄRDE! fel och alla numeriska värden förblir som de är. Detta händer när vi har multiplicerat MID -funktionen med 1.
• IFERROR ((MIDD (A2, RAD (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - När IFERROR -funktionen används tar det bort alla #VÄRDE! fel och bara siffrorna återstår. Utmatningen av den här delen skulle se ut så här - {""; ""; ""; "" ""; "" "" ";" ";" ";" "" "" "" "" "; ””; ””; ””; 1; 0; 0}
• = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””)) - TEXTJOIN -funktionen kombinerar nu helt enkelt strängtecknen som återstår (som endast är siffrorna) och ignorerar den tomma strängen.

Proffstips: Om du vill kontrollera utmatningen av en del av formeln väljer du cellen, trycker på F2 för att komma till redigeringsläget, väljer den del av formeln som du vill ha utdata för och trycker på F9. Du kommer direkt att se resultatet. Och kom ihåg att antingen trycka på Ctrl + Z eller trycka på Escape -tangenten. Tryck INTE på enter -knappen.

Ladda ner exempelfilen

Du kan också använda samma logik för att extrahera textdelen från en alfanumerisk sträng. Nedan är formeln som skulle få textdelen från strängen:

= TEXTJOIN ("", TRUE, IF (ERERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN) (A2))), 1), ""))

En mindre ändring i denna formel är att IF -funktionen används för att kontrollera om matrisen vi får från MID -funktionen är fel eller inte. Om det är ett fel behåller det värdet annars ersätter det det med ett tomt.

Sedan används TEXTJOIN för att kombinera alla texttecken.

Varning: Även om denna formel fungerar utmärkt använder den en flyktig funktion (INDIRECT -funktionen). Det betyder att om du använder detta med en enorm datamängd kan det ta lite tid att ge dig resultaten. Det är bäst att skapa en säkerhetskopia innan du använder den här formeln i Excel.

Extrahera nummer från sträng i Excel (för Excel 2013/2010/2007)

Om du har Excel 2013. 2010. eller 2007 kan du inte använda TEXTJOIN -formeln, så du måste använda en komplicerad formel för att få detta gjort.

Anta att du har en dataset som visas nedan och du vill extrahera alla siffror i strängen i varje cell.

Nedanstående formel kommer att göra detta:

= OM (SUM (LEN (A2) -LEN (ERSTÄLLNING (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (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: $" & LEN (A2)))/10), "")

Om det inte finns något nummer i textsträngen skulle denna formel returnera tom (tom sträng).

Även om detta är en matrisformel, du behöver inte för att använda "Control-Shift-Enter" för att använda detta. En enkel enter fungerar för denna formel.

Tack till denna formel går till det fantastiska Mr. Excel -forumet.

Återigen kommer denna formel att extrahera alla siffror i strängen oavsett position. Om texten till exempel är ”Priset på 10 biljetter är 200 USD”, kommer det att ge dig 10200 som resultat.

Varning: Även om denna formel fungerar utmärkt använder den en flyktig funktion (INDIRECT -funktionen). Det betyder att om du använder detta med en enorm datamängd kan det ta lite tid att ge dig resultaten. Det är bäst att skapa en säkerhetskopia innan du använder den här formeln i Excel.

Separera text och siffror i Excel med hjälp av VBA

Om separering av text och siffror (eller extrahering av siffror från texten) är något du ofta måste, kan du också använda VBA -metoden.

Allt du behöver göra är att använda en enkel VBA -kod för att skapa en anpassad användardefinierad funktion (UDF) i Excel, och sedan använda den VBA -formeln istället för att använda långa och komplicerade formler.

Låt mig visa dig hur du skapar två formler i VBA - en för att extrahera siffror och en för att extrahera text från en sträng.

Extrahera nummer från sträng i Excel (med VBA)

I den här delen kommer jag att visa dig hur du skapar den anpassade funktionen för att bara få den numeriska delen från en sträng.

Nedan finns VBA -koden som vi kommer att använda för att skapa den här anpassade funktionen:

Funktion 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 slutfunktion

Här är stegen för att skapa den här funktionen och sedan använda den i kalkylbladet:

• Gå till fliken Utvecklare.
• Klicka på Visual Basic (Du kan också använda tangentbordsgenvägen ALT + F11)
• I VB Editor-backend som öppnas, högerklicka på något av arbetsbokobjekten.
• Gå till Infoga och klicka på Modul. Detta kommer att infoga modulobjektet för arbetsboken.
• I fönstret Modulkod, kopiera och klistra in VBA -koden som nämns ovan.
• Stäng VB -redigeraren.

Nu kommer du att kunna använda GetText -funktionen i kalkylbladet. Eftersom vi har gjort alla tunga lyft i själva koden är allt du behöver göra att använda formeln = GetNumeric (A2).

Detta ger dig omedelbart bara den numeriska delen av strängen.

Observera att eftersom arbetsboken nu har VBA -kod i den måste du spara den med filtillägget .xls eller .xlsm.

Ladda ner exempelfilen

Om du ofta måste använda den här formeln kan du också spara den i din personliga makro -arbetsbok. Detta gör att du kan använda den här anpassade formeln i alla Excel -arbetsböcker som du arbetar med.

Extrahera text från en sträng i Excel (med VBA)

I den här delen kommer jag att visa dig hur du skapar den anpassade funktionen för att bara få textdelen från en sträng.

Nedan finns VBA -koden som vi kommer att använda för att skapa den här anpassade funktionen:

Funktion GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) För i = 1 Till StringLength Om inte (IsNumeric (Mid (CellRef, i, 1))) Då Resultat = Resultat & Mitt (CellRef, i, 1 ) Nästa i GetText = Resultatavslutningsfunktion

Här är stegen för att skapa den här funktionen och sedan använda den i kalkylbladet:

• Gå till fliken Utvecklare.
• Klicka på Visual Basic (Du kan också använda kortkommandot ALT + F11)
• I VB Editor-backend som öppnas, högerklicka på något av arbetsbokobjekten.
• Gå till Infoga och klicka på Modul. Detta kommer att infoga modulobjektet för arbetsboken.
  • Om du redan har en modul, dubbelklicka på den (du behöver inte infoga en ny om du redan har den).
• I fönstret Modulkod, kopiera och klistra in VBA -koden som nämns ovan.
• Stäng VB -redigeraren.

Nu kommer du att kunna använda GetNumeric -funktionen i kalkylbladet. Eftersom vi har gjort alla tunga lyft i själva koden är allt du behöver göra att använda formeln = GetText (A2).

Detta ger dig omedelbart bara den numeriska delen av strängen.

Observera att eftersom arbetsboken nu har VBA -kod i den måste du spara den med filtillägget .xls eller .xlsm.

Om du ofta måste använda den här formeln kan du också spara den i din personliga makro -arbetsbok. Detta gör att du kan använda den här anpassade formeln i alla Excel -arbetsböcker som du arbetar med.

Om du använder Excel 2013 eller tidigare versioner och inte har det