Hoe extrahereer je alleen de cijfers uit een tekstreeks in Excel?
Bij het werken met Excel komt u regelmatig cellen tegen waarin getallen en tekst door elkaar lopen – zoals „Order 1234” of „Score is 95,6”. Het extraheren van alleen de numerieke waarden uit deze tekstreeksen is vaak essentieel voor verdere analyse, berekeningen of rapportage. Afhankelijk van of het getal aan het begin, einde of ergens in het midden van de tekst staat – of u alleen decimalen nodig heeft – biedt Excel diverse handige methodes om dit eenvoudig te realiseren.
In deze handleiding bespreken we diverse methoden om getallen uit tekstreeksen in Excel te halen, afgestemd op verschillende scenario’s: of het nu gaat om getallen op willekeurige posities, decimale waarden, of juist getallen aan het begin of einde van een reeks. Voor elke methode leggen we duidelijk uit hoe deze werkt, hoe breed toepasbaar ze is en geven we handige tips voor optimaal gebruik.
Getal extraheren vanaf elke positie uit een tekstreeks
Alleen decimaal getal extraheren uit tekstreeks
Getal extraheren vanaf het einde van de tekstreeks
Getal extraheren vanaf het begin van de tekstreeks
Getal extraheren vanaf elke positie uit een tekstreeks
Het extraheren van getallen die overal in een tekstreeks verborgen zitten, is een veelvoorkomende behoefte—vooral bij het opschonen van gegevens uit diverse bronnen of het combineren van informatie van meerdere werkbladen. Voor dit scenario bestaan verschillende praktische en efficiënte oplossingen, elk met zijn eigen ideale toepassing, afhankelijk van uw Excel-versie en uw vertrouwdheid met formules of invoegtoepassingen.
♦ Getal extraheren vanaf elke positie uit een tekstreeks met Flash Fill
Flash Fill is een snelle en handige functie in Excel 2013 en nieuwer waarmee u getallen uit een tekstreeks kunt halen op basis van een patroon dat u aangeeft. Deze oplossing werkt zonder formules of macro’s en is daardoor ideaal voor eenvoudige scenario’s.
1. Typ handmatig in een cel naast uw tekstreeks het getal zoals u dat wilt extraheren (bijvoorbeeld: als A2 „Order123A” bevat, typ dan „123” in B2).
2. Met de actieve cel onder uw handmatige invoer drukt u op Ctrl + E(of gebruikt u het tabblad)Gegevens > Flash Fill). Excel vult automatisch naar beneden en extraheert getallen uit vergelijkbare cellen op basis van uw eerste voorbeeld.
Deze methode is ideaal voor eenmalige, kleine tot middelgrote datasets met een consistente structuur. Houd er rekening mee dat Flash Fill werkt op basis van patroonherkenning – als uw gegevens sterk inconsistent zijn of een wisselende combinatie van cijfers en tekst bevatten, werkt de extractie mogelijk niet zoals verwacht. Als Flash Fill niet automatisch herkent wat u wilt doen, controleer dan of de functie is ingeschakeld via Bestand > Opties > Geavanceerd > Automatisch Flash Fill.
♦ Getal extraheren vanaf elke positie uit een tekstreeks met formules
Het gebruik van formules is een veelzijdige manier om getallen uit tekstreeksen te halen. Deze methode maakt gebruik van ingebouwde of dynamische matrixfuncties en is vooral handig wanneer u een oplossing zoekt die automatisch wordt bijgewerkt zodra de brongegevens wijzigen.
Selecteer een lege cel waarin u het geëxtraheerde getal wilt weergeven. Voer daarna, afhankelijk van uw Excel-versie, één van de onderstaande formules in. Sleep na het invoeren van de formule de vulgreep naar beneden om deze toe te passen op andere cellen. Zie de onderstaande illustratie als referentie:
● Alle Excel-versies:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")Deze formule is compatibel met alle moderne Excel-versies en een betrouwbare keuze als u geen gebruikmaakt van Excel 365 of Excel 2021. Houd er rekening mee dat formules met meerdere functies de prestaties kunnen beïnvloeden bij grote datasets. Zorg er ook voor dat tekstreeksen minstens één numeriek teken bevatten; anders retourneert de formule mogelijk een lege waarde of een fout.
● Excel 365 of Excel 2021 en nieuwere versies:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Deze formule maakt gebruik van de nieuwste dynamische matrixfuncties, waardoor het extractieproces aanzienlijk wordt vereenvoudigd voor gebruikers van Office 365 of Excel 2021 en nieuwer. Ze verwerkt zowel eenvoudige als complexere alfanumerieke combinaties efficiënt. Zorg ervoor dat u een compatibele versie gebruikt, anders werkt de formule mogelijk niet zoals verwacht.
Tip: Controleer bij het gebruik van formules met gemengde of negatieve getallen, of getallen met speciale symbolen, altijd de nauwkeurigheid van de geëxtraheerde resultaten. Standaard herkennen formules mogelijk geen decimale punten; als u decimale getallen wilt extraheren, raadpleegt u daarom de specifieke sectie hieronder. Mocht u een onverwacht resultaat of een foutmelding krijgen, controleer dan uw celverwijzingen en de syntaxis van uw formule.
♦ Getal extraheren vanaf elke positie uit een tekstreeks met Kutools voor Excel
Kutools voor Excel biedt een eenvoudige methode om getallen vanaf elke positie binnen een tekstreeks te extraheren. Deze methode is vooral geschikt voor gebruikers die liever een interactieve interface gebruiken in plaats van formules, of een groot bereik willen verwerken zonder handmatige inspanning. Tijd besparen en stroomlijnt taken door het onthouden of oplossen van formules overbodig te maken.
- Klik op Kutools > Tekst > Tekst extraheren, zoals hieronder wordt weergegeven.
- In het Tekst extraheren-dialoogvenster:
(1.) Selecteer de lijst met cellen die uw tekstreeksen bevatten;
(2.) Kies de optie Nummers extraheren, zodat alleen numerieke delen worden vastgelegd;
(3.) Vink Invoegen als formule aan als u wilt dat geëxtraheerde getallen automatisch worden bijgewerkt wanneer de brongegevens wijzigen (anders zijn de resultaten statische waarden);
(4.) Klik op OK.
- Selecteer, wanneer hierom wordt gevraagd, een cel voor de uitvoer. Kies een leeg gebied met voldoende ruimte voor de resultaten, zodat bestaande gegevens niet worden overschreven.
- Klik op OK. De getallen uit elke geselecteerde cel worden direct geëxtraheerd.
Deze aanpak is gebruiksvriendelijk en vermindert het risico op formulefouten of onjuiste celverwijzingen. Houd er echter rekening mee dat Kutools een invoegtoepassing is en niet standaard in Excel is opgenomen. Het is bijzonder geschikt voor batchverwerking en voor gebruikers die complexe functiesyntaxis of VBA willen vermijden.
Als de getallen niet worden geëxtraheerd zoals verwacht, controleer dan of uw oorspronkelijke tekst gemengde opmaak bevat, zoals verborgen tekens of niet-standaard cijfers. Maak altijd eerst een back-up van uw werkblad voordat u bulkwijzigingen aanbrengt.
♦ Getal extraheren vanaf elke positie uit een tekstreeks met VBA-code
Voor gevorderde gebruikers of specifieke situaties waar formules en invoegtoepassingen ontoereikend zijn, biedt VBA een flexibele oplossing om getallen uit tekstreeksen in Excel te extraheren. Deze aanpak is sterk aanpasbaar en bijzonder geschikt voor het automatiseren van repetitieve taken of het verwerken van niet-standaard datasets.
- Open de VBA-editor door op Alt + F11 te drukken. In het venster Microsoft Visual Basic for Applications klikt u op Invoegen > Module. Plak in het nieuwe modulevenster de volgende code:
- VBA-code: Alleen getal extraheren uit tekstreeks:
Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub - Nadat u de code hebt ingevoegd, drukt u op F5 of klikt u op de knop Uitvoeren. Er verschijnt een promptvenster waarin u het bereik van te verwerken tekstreeksen moet selecteren.
- Nadat u uw bereik hebt geselecteerd, klikt u op OK. Vervolgens verschijnt een tweede prompt waarin u de begincel voor de uitvoer kunt opgeven.
- Klik nogmaals op OK. Alle getallen uit de oorspronkelijke selectie worden geëxtraheerd en geplaatst in het door u opgegeven uitvoergebied.
De VBA-methode biedt efficiënte verwerking van grote datasets en de flexibiliteit om het gedrag naar wens aan te passen — denk aan het extraheren van alleen het eerste getal, het omgaan met decimalen of het uitsluiten van bepaalde symbolen — mits u basiskennis van VBA heeft. Let op: VBA-macro’s kunnen niet ongedaan worden gemaakt en overschrijven gegevens indien het gekozen plaatsingsgebied overlapt met uw oorspronkelijke data. Test daarom altijd eerst op een back-upbestand en zorg dat macro’s in Excel zijn ingeschakeld.
Alleen decimaal getal uit een tekstreeks extraheren
Soms bevatten uw tekstreeksen getallen met decimalen (zoals "gewicht:15,25kg"). Als u specifiek decimale getallen wilt extraheren — inclusief de komma — herkennen standaardformules deze niet altijd correct. De onderstaande oplossingen zijn speciaal ontworpen voor dit soort situaties:
De volgende formules helpen u efficiënt decimale getallen uit tekstreeksen te halen. Voer de juiste formule in een lege cel naast uw gegevens in en sleep de vulgreep naar beneden om deze eventueel op andere rijen toe te passen.
● Alle Excel-versies:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))Deze formule werkt in de meeste Excel-versies en is speciaal ontworpen om decimale punten als onderdeel van een getal te herkennen tijdens extractie. Controleer uw gegevens op overbodige punten in de tekst. Als decimalen niet naar verwachting worden geëxtraheerd, controleer dan de oorspronkelijke reeks op niet-standaardtekens of opmaakproblemen.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")Met Excel 365 verwerken en retourneren dynamische matrices op een intelligente manier decimale getallen uit complexe tekstreeksen. Voer de formule in de doelcel in, druk op Enter en sleep de formule vervolgens naar beneden indien nodig.
Tip: als u herhaaldelijk nullen of foutmeldingen ziet in plaats van getallen, controleer dan of uw gegevens geldige decimale getallen bevatten en of de celverwijzingen correct zijn. Wanneer uw getallen zowel negatieve waarden als decimalen kunnen bevatten, is het mogelijk dat u de formules moet aanpassen.
Getal extraheren vanaf het einde van de tekstreeks
Wanneer getallen altijd aan het einde van uw tekstreeksen staan — bijvoorbeeld "221_ProductID_446" of „923Ticket#2021" — wilt u mogelijk alleen het afsluitende getal extraheren ("446" of "2021"). De formule hieronder is speciaal ontworpen voor dit scenario en haalt uitsluitend numerieke reeksen op die aan het einde van de tekst voorkomen.
Kopieer en plak de volgende formule in een lege cel. In oudere Excel-versies bevestigt u de formule met Ctrl + Shift + Enter om een matrixformule te maken. In nieuwere Excel-versies met dynamische matrices drukt u gewoon op Enter:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))Sleep de vulgreep naar beneden om deze formule op andere rijen toe te passen. Deze methode is ideaal voor consistente opmaak waarbij alleen het laatste getal in elke reeks telt en getallen in het midden of aan het begin worden genegeerd.
Opmerking: als uw gegevens spaties, speciale symbolen of variaties in positie bevatten, pas dan de formule aan of gebruik een algemenere methode zoals hierboven beschreven. Controleer altijd of de uitvoer klopt, vooral wanneer uw tekst eindigt met niet-numerieke tekens die op getallen lijken.
Getal extraheren vanaf het begin van de tekstreeks
Als u alleen de getallen wilt extraheren die helemaal aan het begin van uw tekstreeksen staan, helpt een specifieke formule u dit deel eenvoudig te isoleren.
Plaats de formule in een lege doelcel en gebruik voor oudere Excel-versies Ctrl + Shift + Enterom een matrixformule te maken (in Excel 365/2021 drukt u eenvoudigweg op)Enter):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)Sleep de formule vervolgens naar beneden voor alle rijen waarvoor extractie nodig is. Deze aanpak haalt alleen de getallen aan het begin van de reeks op—ook al staan er elders nog meer getallen, wordt enkel het voorafgaande numerieke gedeelte geretourneerd.
Tip: als het eerste teken in uw reeks niet numeriek is, kan deze formule een lege cel of een fout opleveren. Controleer uw oorspronkelijke gegevens en maak ze indien nodig schoon voor optimale resultaten.
Het extraheren van getallen uit tekstreeksen is een veelvoorkomende en vaak cruciale stap bij het voorbereiden van Excel-gegevens. De beste methode hangt af van de structuur van uw gegevens, uw Excel-versie en uw vertrouwdheid met formules of invoegtoepassingen. Past uw situatie niet bij de bovenstaande oplossingen? Overweeg dan een combinatie van methoden of gebruik gespecialiseerde invoegtoepassingen voor geavanceerdere behoeften. Controleer altijd uw geëxtraheerde resultaten op inconsistenties, bewaar back-ups en test eerst op kleinere gegevenssets om typische problemen te voorkomen.
Gerelateerde artikelen:
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins