Hoe getallen en tekst van string in Excel te splitsen

Anonim

Vaak krijg ik gemengde gegevens van veld en server voor analyse. Deze gegevens zijn meestal vies, met een kolom gemengd met nummer en tekst. Tijdens het opschonen van gegevens vóór analyse, scheid ik getallen en tekst in afzonderlijke kolommen. In dit artikel vertel ik je hoe je dat kunt doen.

Scenario:
Dus een van onze vrienden op Exceltip.com stelde deze vraag in het opmerkingengedeelte. "Hoe scheid ik getallen die voor een tekst komen en aan het einde van de tekst met Excel-formule. Bijvoorbeeld 125EvenueStreet en LoveYou3000 etc.”

Voor het extraheren van tekst gebruiken we RIGHT, LEFT, MID en andere tekstfuncties. We hoeven alleen het aantal te extraheren teksten te weten. En hier zullen we eerst hetzelfde doen.
Nummer en tekst extraheren uit een tekenreeks wanneer het nummer aan het einde van de tekenreeks staat
Voor bovenstaand voorbeeld heb ik dit blad opgesteld. In cel A2 heb ik de string. In cel B2 wil ik het tekstgedeelte en in C2 het Nummergedeelte.

We hoeven dus alleen de positie te weten waar het nummer begint. Dan zullen we links en andere functies gebruiken. Dus om de positie van het eerste nummer te krijgen, gebruiken we de onderstaande generieke formule:
Algemene formule om de positie van het eerste nummer in string te krijgen:

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

Hiermee wordt de positie van het eerste nummer geretourneerd.
Schrijf bijvoorbeeld deze formule in een willekeurige cel.

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

Tekstgedeelte extraheren

Het geeft 15 terug als het eerste nummer dat wordt gevonden op de 15e positie in Tekst. Ik zal het later uitleggen.

Om nu tekst te krijgen, hoeven we van links alleen maar 15-1 tekens uit de tekenreeks te halen. Dus we zullen gebruiken
LEFT-functie om tekst te extraheren.
Formule om tekst van links te extraheren

=LINKS(A5,MIN(ZOEKEN({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)


Hier hebben we zojuist 1 afgetrokken van het getal dat is geretourneerd door MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")).
Uittreksel Nummer Onderdeel

Om nu nummers te krijgen, hoeven we alleen maar cijfertekens te krijgen van het eerste gevonden nummer. We berekenen dus de totale lengte van snaar en trek de positie van het eerste gevonden getal af en tel op 1 ernaar toe. Eenvoudig. Ja, het klinkt gewoon ingewikkeld, het is eenvoudig.
Formule om getallen van rechts te extraheren

=RIGHT(A5,LEN(A5)-MIN(ZOEKEN({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)

Hier hebben we zojuist de totale lengte van de string verkregen met behulp van de LEN-functie en vervolgens de positie van het eerste gevonden nummer afgetrokken en er vervolgens 1 aan toegevoegd. Dit geeft ons het totale aantal getallen. Lees hier meer over het extraheren van tekst met de functies LINKS en RECHTS van Excel.

Het functiegedeelte LINKS en RECHTS is dus eenvoudig. Het lastige deel is MIN en SEARCH Part dat ons de positie van het eerst gevonden nummer geeft. Laten we dat begrijpen.
Hoe het werkt
We weten hoe de functies LINKS en RECHTS werken. We zullen het belangrijkste deel van deze formule onderzoeken dat de positie van het eerste gevonden getal krijgt en dat is: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789 ")
De functie ZOEKEN retourneert de positie van een tekst in een tekenreeks. De functie SEARCH(‘text’,’string’) heeft twee argumenten nodig, eerst de tekst die u wilt doorzoeken en ten tweede de tekenreeks waarin u wilt zoeken.

    • Hier in SEARCH hebben we op tekstpositie een reeks getallen van 0 tot 9. En op stringpositie hebben we een string die is samengevoegd met "0123456789" met behulp van & exploitant. Waarom? Ik zal het je vertellen.
    • Elk element in de array {0,1,2,3,4,5,6,7,8,9} wordt doorzocht in de gegeven string en retourneert zijn positie in array-vorm string op dezelfde index in array.
    • Als een waarde niet wordt gevonden, zal dit een fout veroorzaken. Vandaar dat alle formules resulteren in een fout. Om dit te voorkomen, hebben we de nummers "0123456789" in tekst aaneengeschakeld. Zodat het altijd elk nummer in string vindt. Deze cijfers zijn uiteindelijk dus geen probleem.
    • Nu retourneert de MIN-functie de kleinste waarde uit de array die wordt geretourneerd door de SEARCH-functie. Deze kleinste waarde is het eerste getal in de tekenreeks. Met behulp van deze NUMBER- en LEFT- en RIGHT-functie kunnen we de tekst- en tekenreeksdelen splitsen.

Laten we ons voorbeeld eens bekijken. In A5 hebben we de string met straatnaam en huisnummer. We moeten ze scheiden in verschillende cellen.
Laten we eerst eens kijken hoe we onze positie van het eerste nummer in de reeks hebben gekregen.

    • MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): dit vertaalt zich in MIN(SEARCH({0,1,2,3, 4,5,6,7,8,9},”Monta270123456789”))

Nu, zoals ik heb uitgelegd, zal zoeken elk nummer in array {0,1,2,3,4,5,6,7,8,9} zoeken in Monta270123456789 en zal zijn positie in een matrixvorm teruggeven. De geretourneerde array is {8,9,6,11,12,13,14,7,16,17}. Hoe?
0 wordt gezocht in string. Het is te vinden op 8 positie. Vandaar dat ons eerste element 8 is. Merk op dat onze originele tekst slechts 7 karakters lang is. Snap je. 0 is geen onderdeel van Monta27.
Volgende 1 wordt gezocht in string en het maakt ook geen deel uit van de originele string, en we krijgen positie 9.
Volgende 2 zullen worden doorzocht. Omdat het het deel is van de originele string, krijgen we de index als 6.
Evenzo wordt elk element op een bepaalde positie gevonden.

    • Nu wordt deze array doorgegeven aan de MIN-functie als MIN({8,9,6,11,12,13,14,7,16,17}). MIN geeft de 6 terug die de positie is van het eerste getal dat in de originele tekst wordt gevonden.
      En het verhaal hierna is vrij eenvoudig. We gebruiken dit nummer om tekst en cijfers te extraheren met de LINKS- en RECHTS-functie.

Nummer en tekst extraheren uit een tekenreeks als het nummer aan het begin van een tekenreeks staat
In het bovenstaande voorbeeld stond Getal aan het einde van de tekenreeks. Hoe extraheren we nummer en tekst als nummer in het begin staat.

Ik heb een soortgelijke tabel opgesteld als hierboven. Het heeft alleen een nummer in het begin.

Hier zullen we een andere techniek gebruiken. We tellen de lengte van de getallen (die hier 2 is) en extraheren dat aantal tekens aan de linkerkant van String.
Dus de methode is =LEFT (string, aantal getallen)
Om het aantal tekens te tellen, is dit de formule.
Generieke formule om het aantal getallen te tellen:

=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8" ,"9"},""))

Hier,

      • De SUBSTITUTE-functie vervangt elk gevonden nummer door "" (leeg). Als een getal wordt gevonden dat gesubstitueerd is en er wordt een nieuwe string aan de array toegevoegd, anders wordt de originele string aan de array toegevoegd. Op deze manier hebben we een array van 10 strings.
      • De functie LEN retourneert nu de lengte van tekens in een array van die tekenreeksen.
      • Vervolgens trekken we van de lengte van de originele tekenreeksen de lengte af van elke tekenreeks die wordt geretourneerd door de SUBSTITUTE-functie. Dit zal opnieuw een array retourneren.
      • Nu zal SUM al deze getallen optellen. Dit is het aantal getallen in string.

Nummerdeel uit string halen

Nu we de lengte van getallen weten in snaar, we zullen deze functie in LEFT vervangen.
Aangezien we onze string een A11 hebben, is onze:

Formule om getallen van LINKS te extraheren

=LEFT(A11,SUM(LEN(A11)-LEN(VERVANG(A11,{"0","1","2","3","4","5","6","7" ,"8","9"},""))))


Tekstgedeelte uit String extraheren

Omdat we het aantal getallen kennen, kunnen we het aftrekken van de totale lengte van de tekenreeks om het aantal alfabetten in de tekenreeks te krijgen en vervolgens de rechterfunctie gebruiken om dat aantal tekens aan de rechterkant van de tekenreeks te extraheren.

Formule om tekst uit RECHTS te extraheren

=RIGHT(A11,LEN(A2)-SUM(LEN(A11)-LEN(VERVANG(A11,{"0","1","2","3","4","5","6 ","7","8","9"},""))))


Hoe het werkt
Het belangrijkste deel van beide formules is SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6"," 7","8","9"},""))) die het eerste voorkomen van een getal berekent. Pas nadat we dit hebben gevonden, kunnen we tekst en nummer splitsen met de LEFT-functie. Dus laten we dit begrijpen.

      • VERVANG(A11,{"0","1","2","3","4","5","6","7","8","9"},""): Dit deel retourneert een reeks tekenreeksen in A11 nadat deze getallen zijn vervangen door niets/leeg (""). Voor 27Monta het geeft {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"} terug.
      • LENTE(VERVANGING(A11,{"0","1","2","3","4","5","6","7","8","9"},"" )): Nu wordt het SUBSTITUTE-gedeelte ingepakt door de LEN-functie. Deze retourneert de lengte van teksten in de array die wordt geretourneerd door de SUBSTITUTE-functie. Als resultaat hebben we {7,7,6,7,7,7,7,6,7,7}.
      • LEN(A11)-LENGTE(VERVANGING(A11,{"0","1","2","3","4","5","6","7","8","9 "},"")): Hier trekken we elk getal dat wordt geretourneerd door het bovenstaande deel af van de lengte van de werkelijke tekenreeks. Lengte van de originele tekst is 7. We hebben dus {7-7,7-7,7-6,… .}. Uiteindelijk hebben we {0,0,1,0,0,0,0,1,0,0}.
      • SUM(LEN(A11)-LEN(VERVANG(A11,{"0","1","2","3","4","5","6","7","8", "9"},""))): Hier hebben we SUM gebruikt om de array op te tellen die wordt geretourneerd door het bovenstaande deel van de functie. Dit geeft 2. Dat is het aantal getallen in een string.

Als we dit nu gebruiken, kunnen we de teksten en het nummer extraheren en in verschillende cellen splitsen. Deze methode werkt met zowel teksttype, wanneer het nummer aan het begin staat en wanneer het aan het einde is. U hoeft alleen de functies LINKS en RECHTS op de juiste manier te gebruiken.
Gebruik de functie SplitNumText om getallen en teksten van een tekenreeks te splitsen
De bovenstaande methoden zijn een beetje ingewikkeld en ze zijn niet handig als tekst en cijfers worden gemengd. Gebruik deze door de gebruiker gedefinieerde functie om tekst en cijfers te splitsen.

Syntaxis:

=SplitGetalText(string, op)

Snaar: De string die u wilt splitsen.
Op: dit is booleaans. Pass 0 of vals tekstgedeelte te krijgen. Voor nummer onderdeel, pass waar of elk getal groter dan 0.
Als de tekenreeks bijvoorbeeld in A20 staat,
Formule voor het extraheren van getallen uit string is:

=SplitGetalTekst(A20,1)

En
Formule voor het extraheren van tekst uit string is:

=SplitGetalTekst(A20,0)

Kopieer onderstaande code in de VBA-module om de bovenstaande formule te laten werken.

Functie 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 Anders SplitNumText = txt End If End Functie

Deze code controleert eenvoudig elk teken in een reeks, of het een getal is of niet. Als het een getal is, wordt het opgeslagen in de variabele num, anders in de variabele txt. Als de gebruiker true doorgeeft voor op, wordt num geretourneerd, anders wordt txt geretourneerd.

Dit is naar mijn mening de beste manier om nummer en tekst van een string te splitsen.
Je kunt het werkboek hier downloaden als je wilt.

Dus ja jongens, dit zijn de manieren om tekst en cijfers in verschillende cellen te splitsen. Laat het me weten als je twijfels of een betere oplossing hebt in de comments hieronder. Het is altijd leuk om met jongens om te gaan.

Klik op de onderstaande link om het werkbestand te downloaden:

Nummer en tekst uit een cel splitsen

Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?