Hur man delar nummer och text från sträng i Excel

Många gånger får jag blandade data från fält och server för analys. Dessa data är vanligtvis smutsiga, med kolumn blandad med nummer och text. När jag gör datarengöring innan analysen separerar jag siffror och text i separata kolumner. I den här artikeln berättar jag hur du kan göra det.

Scenario:
Så en av våra vänner på Exceltip.com ställde denna fråga i kommentarsfältet. ”Hur skiljer jag siffror som kommer före en text och i slutet av texten med Excel Formula. Till exempel 125EvenueStreet och LoveYou3000 etc. ”

För att extrahera text använder vi HÖGER, VÄNSTER, MID och andra textfunktioner. Vi behöver bara veta hur många texter vi ska extrahera. Och här kommer vi att göra detsamma först.
Extrahera nummer och text från en sträng när talet är i slutet av strängen
För ovanstående exempel har jag förberett det här bladet. I cell A2 har jag strängen. I cell B2 vill jag ha textdelen och i C2 nummerdelen.

Så vi behöver bara veta positionen varifrån siffran börjar. Då kommer vi att använda vänster och annan funktion. Så för att få positionen för första numret använder vi nedan generisk formel:
Generisk formel för att få position för första nummer i sträng:

= MIN (SÖK ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Detta returnerar positionen för första numret.
För exemplet ovan skriver du denna formel i valfri cell.

= MIN (SÖK ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))

Extrahera textdel

Det kommer att returnera 15 eftersom det första numret som hittas är på 15: e plats i Text. Jag förklarar det senare.

Nu, för att få text, från vänster behöver vi bara få 15-1 tecken från strängen. Så vi kommer att använda
VÄNSTER -funktion för att extrahera text.
Formel för att extrahera text från vänster

= VÄNSTER (A5, MIN (SÖK ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)

Här har vi precis subtraherat 1 från det någonsin tal som returneras av MIN (SÖK ({0,1,2,3,4,5,6,7,8,9}, A5 och "0123456789")).
Utdrag nummer del

För att få siffror behöver vi bara få siffror från det första numret som hittats. Så vi beräknar den totala längden på sträng och subtrahera positionen för första nummer som hittats och lägg till 1 till den. Enkel. Ja det låter bara komplext, det är enkelt.
Formel för att extrahera siffror från höger

= HÖGER (A5, LEN (A5) -MIN (SÖK ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))+1)

Här fick vi bara den totala stränglängden med LEN -funktionen och drog sedan av positionen för det första hittade numret och lade sedan till 1 till det. Detta ger oss totalt antal nummer. Läs mer här om hur du extraherar text med hjälp av VÄNSTER- och HÖGER -funktioner i Excel.

Så funktionen VÄNSTER och HÖGER är enkel. Den knepiga delen är MIN och SÖK -del som ger oss positionen för först hittade nummer. Låt oss förstå det.
Hur det fungerar
Vi vet hur VÄNSTER och HÖGER fungerar. Vi kommer att utforska huvuddelen av denna formel som får positionen för första numret hittat och det är: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
SEARCH -funktionen returnerar positionen för en text i strängen. SEARCH ('text', 'string') -funktionen tar två argument, först texten du vill söka, andra strängen där du vill söka.

- Här i SÖK, vid textposition har vi en rad nummer från 0 till 9. Och vid strängposition har vi sträng som är sammanfogad med "0123456789" med & operatör. Varför? Jag ska berätta för dig.
- Varje element i matrisen {0,1,2,3,4,5,6,7,8,9} kommer att sökas i en given sträng och returnerar sin position i matrisformsträng vid samma index i matrisen.
- Om något värde inte hittas kommer det att orsaka ett fel. All formel resulterar därför i ett fel. För att undvika detta sammanfogade vi siffrorna "0123456789" i text. Så att det alltid hittar varje nummer i strängen. Dessa siffror är i slutändan och kommer därför inte att orsaka några problem.
- Nu returnerar MIN -funktionen det minsta värdet från array som returneras av SEARCH -funktionen. Det minsta värdet är det första talet i strängen. Nu med denna NUMBER och VÄNSTER och HÖGER funktion kan vi dela upp text och strängdelar.

Låt oss undersöka vårt exempel. I A5 har vi strängen som har gatunamn och husnummer. Vi måste separera dem i olika celler.
Låt oss först se hur vi fick vår position som första nummer i sträng.

- MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): detta kommer att översättas till MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Nu, som jag förklarade, kommer sökningen att söka efter varje nummer i array {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 och kommer att returnera sin position i en matrisform. Den returnerade gruppen kommer att vara {8,9,6,11,12,13,14,7,16,17}. Hur?
0 kommer att sökas i sträng. Det finns på 8 position. Därför är vårt första element 8. Observera att vår originaltext bara är 7 tecken lång. Förstår. 0 är inte en del av Monta27.
Nästa 1 kommer att sökas i sträng och det är inte heller en del av originalsträngen, och vi får position 9.
Nästa 2 kommer att sökas. Eftersom det är delen av originalsträngen får vi dess index som 6.
På samma sätt finns varje element i någon position.

- Nu skickas denna array till MIN -funktionen som MIN ({8,9,6,11,12,13,14,7,16,17}). MIN returnerar 6: an som är positionen för första numret som finns i originaltexten.
  Och historien efter detta är ganska enkel. Vi använder detta nummer extrahera text och siffror med hjälp av VÄNSTER och HÖGER funktion.

Extrahera nummer och text från en sträng när tal är i början av sträng
I exemplet ovan var Number i slutet av strängen. Hur extraherar vi tal och text när tal är i början.

Jag har förberett en liknande tabell som ovan. Det har bara nummer i början.

Här kommer vi att använda en annan teknik. Vi räknar längden på siffror (vilket är 2 här) och extraherar det antalet tecken från vänster om strängen.
Så metoden är = VÄNSTER (sträng, antal siffror)
För att räkna antalet tecken är detta formeln.
Generisk formel för att räkna antalet nummer:

= SUMMA (LEN (sträng) -LEN (SUBSTITUTE (sträng, {"0", "1", "2", "3", "4", "5", "6", "7", "8") , "9"}, ""))

Här,

- - SUBSTITUTE -funktionen ersätter varje nummer som hittas med “” (tomt). Om ett nummer hittas ti substituerat och ny sträng kommer att läggas till array, kommer andra kloka originalsträngar att läggas till i arrayen. På detta sätt kommer vi att ha en uppsättning av 10 strängar.
  - Nu kommer LEN -funktionen att returnera teckenlängden i en rad av dessa strängar.
  - Sedan, från originalsträngarnas längd, kommer vi att subtrahera längden på varje sträng som returneras av funktionen SUBSTITUTE. Detta kommer igen att returnera en matris.
  - Nu kommer SUM att lägga till alla dessa nummer. Detta är antalet nummer i strängen.

Extrahera antal delar från strängen

Nu eftersom vi vet längden på siffror i sträng, vi kommer att ersätta denna funktion med VÄNSTER.
Eftersom vi har vår sträng en A11 vår:

Formel för att extrahera siffror från VÄNSTER

= VÄNSTER (A11, SUM (LEN (A11) -LEN (ERSTÄLLNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7") , "8", "9"}, "")))))

Extrahera textdel från sträng

Eftersom vi vet antal nummer kan vi subtrahera det från strängens totala längd för att få talalfabet i strängen och sedan använda rätt funktion för att extrahera det antalet tecken från höger om strängen.

Formel för att extrahera text från HÖGER

= HÖGER (A11, LEN (A2) -SUM (LEN (A11) -LEN (ERSÄTTNING (A11, {"0", "1", "2", "3", "4", "5", "6) "," 7 "," 8 "," 9 "}," ")))))

Hur det fungerar
Huvuddelen i båda formlerna är SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," ")))) som beräknar den första förekomsten av ett tal. Först efter att ha hittat detta kan vi dela upp text och nummer med VÄNSTER -funktionen. Så låt oss förstå detta.

- - BYTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Denna del returnerar en rad strängar i A11 efter att dessa nummer har ersatts med ingenting/tomt (“”). För 27 Monta den returnerar {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
  - LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nu byts SUBSTITUTE -delen ut med LEN -funktionen. Denna returlängd för texter i array som returneras av funktionen SUBSTITUTE. Som ett resultat kommer vi att ha {7,7,6,7,7,7,7,6,7,7}.
  - LEN (A11) -LEN (ERSÄTTNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9) "}," ")): Här subtraherar vi varje tal som returneras av ovanstående del från den faktiska strängens längd. Originaltextens längd är 7. Därför kommer vi att ha {7-7,7-7,7-6,….}. Slutligen kommer vi att ha {0,0,1,0,0,0,0,1,0,0}.
  - SUMMA (LENG (A11) -LEN (ERSTÄLLNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")))): Här använde vi SUM för att summera matrisen som returneras av en del av funktionen. Detta ger 2. Vilket är antalet nummer i strängen.

Nu kan vi extrahera texter och nummer och dela dem i olika celler. Denna metod fungerar med både typtext, när tal är i början och när det är i slutändan. Du behöver bara använda vänster och höger funktion på lämpligt sätt.
Använd SplitNumText -funktionen för att dela nummer och texter från en sträng
Ovanstående metoder är lite komplexa och de är inte användbara när text och siffror blandas. Använd denna användardefinierade funktion för att dela upp text och siffror.

Syntax:

= SplitNumText (sträng, op)

Sträng: Strängen du vill dela.
Op: det här är booleskt. Pass 0 eller falsk för att få textdel. För nummerdel, passera Sann eller ett tal som är större än 0.
Till exempel, om strängen är i A20 då,
Formel för att extrahera tal från sträng är:

= SplitNumText (A20,1)

Och
Formel för att extrahera text från sträng är:

= SplitNumText (A20,0)

Kopiera koden nedan i VBA -modulen så att formeln ovan fungerar.

Funktion SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Then num = num & Mid (str, i , 1) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function

Denna kod kontrollerar helt enkelt varje tecken i sträng, om det är ett tal eller inte. Om det är ett tal lagras det i num -variabel annars i txt -variabel. Om användaren passerar true för op returneras num returnerat annars returneras txt.

Detta är det bästa sättet att dela nummer och text från en sträng enligt mig.
Du kan ladda ner arbetsboken här om du vill.

Så ja killar, det här är sätten att dela upp text och siffror i olika celler. Låt mig veta om du har några tvivel eller någon bättre lösning i kommentarfältet nedan. Det är alltid kul att interagera med killar.

Klicka på länken nedan för att ladda ner arbetsfilen:

Dela nummer och text från en cell

Populära artiklar:
50 Excel -genvägar för att öka din produktivitet
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Hur man använder SUMIF -funktionen i Excel