Bekijk video - Hoe getallen uit een tekststring in Excel te extraheren (met formule en VBA)
Er is geen ingebouwde functie in Excel om de getallen uit een tekenreeks in een cel te extraheren (of omgekeerd - verwijder het numerieke gedeelte en extraheer het tekstgedeelte uit een alfanumerieke reeks).
Dit kan echter worden gedaan met behulp van een cocktail van Excel-functies of een eenvoudige VBA-code.
Laat me je eerst laten zien waar ik het over heb.
Stel dat je een dataset hebt zoals hieronder getoond en je wilt de getallen uit de string halen (zoals hieronder getoond):
De methode die u kiest, is ook afhankelijk van de versie van Excel die u gebruikt:
- Voor versies vóór Excel 2016 moet u iets langere formules gebruiken
- Voor Excel 2016 kunt u de nieuw geïntroduceerde TEXTJOIN-functie gebruiken
- VBA-methode kan in alle versies van Excel worden gebruikt
Klik hier om het voorbeeldbestand te downloaden
Extraheer getallen uit string in Excel (Formule voor Excel 2016)
Deze formule werkt alleen in Excel 2016 omdat deze de nieuw geïntroduceerde TEXTJOIN-functie gebruikt.
Deze formule kan ook de getallen extraheren die aan het begin, het einde of het midden van de tekstreeks staan.
Merk op dat de TEXTJOIN-formule die in deze sectie wordt behandeld, u alle numerieke tekens samen zou geven. Als de tekst bijvoorbeeld 'De prijs van 10 tickets is USD 200' is, krijgt u 10200 als resultaat.Stel dat je de dataset hebt zoals hieronder getoond en je wilt de getallen uit de strings in elke cel halen:
Hieronder vindt u de formule waarmee u een numeriek deel van een tekenreeks in Excel krijgt.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,RIJ(INDIRECT("1:"&LEN(A2))),1)*1),""))
Dit is een matrixformule, dus je moet 'Control + Shift + Enter' in plaats van Enter te gebruiken.
Als er geen getallen in de tekenreeks staan, zou deze formule een blanco (lege tekenreeks) retourneren.
Hoe werkt deze formule?
Laat me deze formule doorbreken en proberen uit te leggen hoe het werkt:
- ROW(INDIRECT(“1:”&LEN(A2))) - dit deel van de formule geeft een reeks getallen vanaf één. De functie LEN in de formule retourneert het totale aantal tekens in de tekenreeks. In het geval van "De kosten zijn USD 100", wordt 19 geretourneerd. De formules worden dus ROW(INDIRECT("1:19"). De ROW-functie retourneert dan een reeks getallen - {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) - Dit deel van de formule zou een array van #VALUE! fouten of getallen op basis van de tekenreeks. Alle teksttekens in de tekenreeks worden #WAARDE! fouten en alle numerieke waarden blijven zoals ze zijn. Dit gebeurt omdat we de MID-functie met 1 hebben vermenigvuldigd.
- IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"") - Als de IFERROR-functie wordt gebruikt, worden alle #VALUE! fouten en alleen de cijfers zouden overblijven. De uitvoer van dit deel zou er als volgt uitzien: {“”;””;””;””;””;””;””;””;””;””;””;””;””; ””;””;””;1;0;0}
- =TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) - De TEXTJOIN-functie combineert nu eenvoudig de tekenreekstekens dat blijft staan (wat alleen de cijfers zijn) en negeert de lege string.
Download het voorbeeldbestand
U kunt dezelfde logica ook gebruiken om het tekstgedeelte uit een alfanumerieke tekenreeks te extraheren. Hieronder staat de formule die het tekstgedeelte uit de tekenreeks zou halen:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN (A2))),1),""))
Een kleine wijziging in deze formule is dat de ALS-functie wordt gebruikt om te controleren of de array die we van de MID-functie krijgen fouten zijn of niet. Als het een fout is, behoudt het de waarde, anders wordt het vervangen door een spatie.
Vervolgens wordt TEXTJOIN gebruikt om alle teksttekens te combineren.
Voorzichtigheid: Hoewel deze formule prima werkt, gebruikt deze een vluchtige functie (de INDIRECT-functie). Dit betekent dat als u dit met een enorme dataset gebruikt, het enige tijd kan duren om u de resultaten te geven. U kunt het beste een back-up maken voordat u deze formule in Excel gebruikt.Extraheer getallen uit string in Excel (voor Excel 2013/2010/2007)
Als je Excel 2013. 2010 of 2007 hebt, kun je de TEXTJOIN-formule niet gebruiken, dus je zult een ingewikkelde formule moeten gebruiken om dit voor elkaar te krijgen.
Stel dat u een gegevensset hebt zoals hieronder weergegeven en dat u alle getallen in de tekenreeks in elke cel wilt extraheren.
De onderstaande formule zal dit voor elkaar krijgen:
=IF(SUM(LEN(A2)-LEN(VERVANG(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SOMPRODUCT(MID(0&A2, LARGE(INDEX(ISGETAL(--MID(A2,RIJ(INDIRECT("$1:$"&LEN(A2))), 1))* RIJ(INDIRECT("$1:$"&LEN(A2))),0), RIJ(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Als er geen getal in de tekenreeks staat, retourneert deze formule blanco (lege tekenreeks).
Hoewel dit een matrixformule is, moet u niet nodig om 'Control-Shift-Enter' te gebruiken om dit te gebruiken. Een simpele enter werkt voor deze formule.
De eer voor deze formule gaat naar het geweldige Mr. Excel-forum.
Nogmaals, deze formule extraheert alle getallen in de tekenreeks, ongeacht de positie. Als de tekst bijvoorbeeld 'De prijs van 10 tickets is USD 200' is, krijgt u 10200 als resultaat.
Voorzichtigheid: Hoewel deze formule prima werkt, gebruikt deze een vluchtige functie (de INDIRECT-functie). Dit betekent dat als u dit met een enorme dataset gebruikt, het enige tijd kan duren om u de resultaten te geven. U kunt het beste een back-up maken voordat u deze formule in Excel gebruikt.Scheid tekst en cijfers in Excel met VBA
Als het scheiden van tekst en getallen (of het extraheren van getallen uit de tekst) iets is dat je vaak moet doen, kun je ook de VBA-methode gebruiken.
Het enige dat u hoeft te doen, is een eenvoudige VBA-code gebruiken om een aangepaste door de gebruiker gedefinieerde functie (UDF) in Excel te maken, en in plaats van lange en gecompliceerde formules te gebruiken, gebruikt u die VBA-formule.
Laat me je laten zien hoe je twee formules in VBA kunt maken - een om getallen te extraheren en een om tekst uit een tekenreeks te extraheren.
Extraheer nummers uit String in Excel (met VBA)
In dit deel laat ik je zien hoe je de aangepaste functie maakt om alleen het numerieke deel van een string te krijgen.
Hieronder staat de VBA-code die we zullen gebruiken om deze aangepaste functie te maken:
Functie 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 = Resultaat Eindfunctie
Hier zijn de stappen om deze functie te maken en vervolgens in het werkblad te gebruiken:
- Ga naar het tabblad Ontwikkelaars.
- Klik op Visual Basic (U kunt ook de sneltoets ALT + F11 gebruiken)
- Klik in de VB Editor-backend die wordt geopend met de rechtermuisknop op een van de werkmapobjecten.
- Ga naar Invoegen en klik op Module. Hiermee wordt het moduleobject voor de werkmap ingevoegd.
- Kopieer en plak in het venster Modulecode de hierboven genoemde VBA-code.
- Sluit de VB-editor.
Nu kunt u de GetText-functie in het werkblad gebruiken. Aangezien we al het zware werk in de code zelf hebben gedaan, hoeft u alleen maar de formule =GetNumeric(A2) te gebruiken.
Dit geeft u onmiddellijk alleen het numerieke deel van de tekenreeks.
Merk op dat aangezien de werkmap nu VBA-code bevat, u deze moet opslaan met de extensie .xls of .xlsm.
Download het voorbeeldbestand
Als u deze formule vaak moet gebruiken, kunt u deze ook opslaan in uw persoonlijke macrowerkmap. Hierdoor kunt u deze aangepaste formule gebruiken in elk van de Excel-werkmappen waarmee u werkt.
Extraheer tekst uit een tekenreeks in Excel (met VBA)
In dit deel laat ik je zien hoe je de aangepaste functie kunt maken om alleen het tekstgedeelte van een tekenreeks te krijgen.
Hieronder staat de VBA-code die we zullen gebruiken om deze aangepaste functie te maken:
Functie GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Indien niet (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Volgende i GetText = Resultaat Einde Functie
Hier zijn de stappen om deze functie te maken en vervolgens in het werkblad te gebruiken:
- Ga naar het tabblad Ontwikkelaars.
- Klik op Visual Basic (U kunt ook de sneltoets ALT + F11 gebruiken)
- Klik in de VB Editor-backend die wordt geopend met de rechtermuisknop op een van de werkmapobjecten.
- Ga naar Invoegen en klik op Module. Hiermee wordt het moduleobject voor de werkmap ingevoegd.
- Als je al een module hebt, dubbelklik er dan op (je hoeft geen nieuwe in te voegen als je die al hebt).
- Als je al een module hebt, dubbelklik er dan op (je hoeft geen nieuwe in te voegen als je die al hebt).
- Kopieer en plak in het venster Modulecode de hierboven genoemde VBA-code.
- Sluit de VB-editor.
Nu kunt u de GetNumeric-functie in het werkblad gebruiken. Aangezien we al het zware werk in de code zelf hebben gedaan, hoeft u alleen maar de formule =GetText(A2) te gebruiken.
Dit geeft u onmiddellijk alleen het numerieke deel van de tekenreeks.
Merk op dat aangezien de werkmap nu VBA-code bevat, u deze moet opslaan met de extensie .xls of .xlsm.
Als u deze formule vaak moet gebruiken, kunt u deze ook opslaan in uw persoonlijke macrowerkmap. Hierdoor kunt u deze aangepaste formule gebruiken in elk van de Excel-werkmappen waarmee u werkt.
Als u Excel 2013 of eerdere versies gebruikt en deze niet hebt:
