Hoe vindt u het eerste of alle cijfers in een tekstreeks in Excel?

Deze methode introduceert een reeks formules die niet alleen het eerste getal in een tekstreeks kunnen opsporen en extraheren, maar ook de exacte positie bepalen waar dat cijfer in de cel voorkomt. Deze formules zijn vooral handig voor gebruikers die gestandaardiseerde reeksen moeten analyseren met variabele getalposities, zoals productcodes of ID’s die zijn ingebed in beschrijvende tekst.

Zoek en extraheer het eerste getal in een tekstreeks met een matrixformule

Selecteer een lege cel waarin u het eerste getal uit een tekstreeks wilt ophalen, voer de formule =MID(A2,MIN(IF((ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2)))),ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2))))),1)+0 in (A2 is de cel met de tekst waarin u het eerste getal zoekt) en druk tegelijkertijd op Ctrl+Shift+Enter. Dankzij deze matrixformule wordt elk teken in uw tekstreeks individueel geanalyseerd.
Sleep na bevestiging de vulgreep van de cel omlaag om de formule indien nodig op aanvullende rijen toe te passen.

Na toepassing worden de eerste getallen van elke tekstreeks gevonden en geëxtraheerd, zoals hieronder wordt weergegeven:

Zoek de positie van het eerste getal in een tekstreeks met formules

Selecteer een lege cel waarin u de positie van het eerste cijfer wilt weergeven en voer de volgende formule in:=MIN(IF(ISGETAL(VIND({1,2,3,4,5,6,7,8,9,0},A2)),VIND({1,2,3,4,5,6,7,8,9,0},A2))) (A2 staat hier voor de cel die u wilt controleren). Deze formule bepaalt de vroegste positie van een cijfer (0–9). Sleep de formule omlaag om aangrenzende cellen automatisch in te vullen, indien nodig.

Opmerking: U kunt ook deze matrixformule gebruiken:=MIN(VIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9})). Vergeet niet deze te bevestigen met Ctrl+Shift+Enter. Wanneer u formules kopieert, moet u vaste celverwijzingen altijd aanpassen als uw structuur wijzigt. Als de reeks geen getallen bevat, geven deze formules een foutmelding. U kunt de formule insluiten in ALS.FOUT om in dergelijke gevallen een aangepast bericht weer te geven.

Als uw doel is om alle getallen uit een cel te halen door simpelweg alle niet-numerieke tekens te verwijderen, biedt de Specifieke tekens verwijderen-functie van Kutools voor Excel een handige oplossing. Deze functie is bijzonder geschikt voor gebruikers die regelmatig geïmporteerde gegevens verwerken of snel meerdere cellen willen opschonen, en die een eenvoudige interface verkiezen boven complexe formules.

1. Selecteer het bereik dat u wilt verwerken om alle getallen uit te halen en ga naar het menu: Kutools > Tekst > Specifieke tekens verwijderen. Zie de schermafbeelding ter ondersteuning:

2. In het verschijnende dialoogvenster Specifieke tekens verwijderen zorgt u ervoor dat alleen het selectievakje Niet-numeriek is aangevinkt en klikt u op OK om door te gaan.

Als resultaat verwijdert de tool alle niet-numerieke tekens uit de geselecteerde cellen, zodat u alleen de numerieke onderdelen van elke cel overhoudt.

Overweeg vóór het uitvoeren van deze bewerking een back-up te maken van uw bereik voor het geval u wilt terugkeren. Voor tekstreeksen met getallen gescheiden door letters (bijv. "A23B17") worden alle getallen samengevoegd („2317”). Als u een andere indeling verwacht, kunt u deze functie combineren met andere functies.

Als u direct alle getallen binnen een tekstcel (ongeacht het formaat) als één waarde wilt extraheren, biedt de functie EXTRACTNUMBERS in Kutools voor Excel een gebruiksvriendelijke en zeer efficiënte oplossing. Dit is vooral handig voor gebruikers die batchverwerking nodig hebben, maar liever kiezen voor een functiegebaseerde, formulematige aanpak die flexibel in het werkblad kan worden toegepast.

1. Selecteer een lege cel waarin de geëxtraheerde getallen moeten verschijnen en klik op Kutools > Functies > Tekst > EXTRACTNUMBERS. Zie de onderstaande afbeelding:

2. Wijs in het dialoogvenster Functieargumenten de relevante tekstcel toe aan het veld Txt en voer WAAR in het veld N in (om een numerieke waarde terug te geven). Klik vervolgens op OK om te bevestigen en nummers te extraheren.

Opmerkingen:
(1) U kunt WAAR, ONWAAR invoeren of het veld N leeg laten. Door WAAR op te geven, wordt een numerieke waarde geretourneerd; ONWAAR of het leeg laten levert de getallen als tekst op (handig om voorloopnullen te behouden).
(2) Deze functie kunt u ook rechtstreeks als formule in het werkblad gebruiken: =EXTRACTNUMBERS(A2,WAAR). Voer deze formule in de gewenste uitvoercel in.
(3) Zorg er bij het kopiëren van de formule naar aangrenzende rijen voor dat u de celverwijzingen indien nodig aanpast.

De geëxtraheerde getallen uit elke tekstcel worden weergegeven zoals hieronder is geïllustreerd:

Wanneer formules en ingebouwde functies niet ver genoeg reiken—bijvoorbeeld als u alleen het eerste getal, de eerste complete getalreeks of alle afzonderlijke getallen uit een reeks wilt extraheren—biedt VBA-code meer flexibiliteit en automatisering. Deze methode is vooral geschikt voor gebruikers met regelmatig unieke extractiebehoeften bij grote datasets of voor wie het proces wil automatiseren met herbruikbare macro’s. Zorg ervoor dat macro’s zijn ingeschakeld en maak altijd eerst een back-up van uw bestand voordat u code uitvoert op grote datasets.

1. Klik in het hoofdvenster van Excel op Ontwikkelaarshulpmiddelen > Visual Basic om de VBA-editor te openen. Selecteer in het venster Microsoft Visual Basic for Applications Invoegen > Module. Kopieer en plak de volgende code in de nieuwe module:

Extraheer de eerste getallen: Haalt alleen de eerste aaneengesloten reeks cijfers uit elke tekenreeks (bijvoorbeeld: „abc12def45” levert 12 op).

Sub ExtractFirstNumber()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim sNum As String
    Dim FirstNum As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        sNum = ""
        FirstNum = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                sNum = sNum & Mid(txt, i, 1)
                
                If i = Len(txt) Or Not (Mid(txt, i + 1, 1) Like "[0-9]") Then
                    FirstNum = sNum
                    Exit For
                End If
            ElseIf sNum <> "" Then
                FirstNum = sNum
                Exit For
            End If
        Next i
        
        cell.Offset(0, 1).Value = FirstNum
    Next cell
End Sub

Extraheer alle getallen: Haalt alle getallen uit de tekst in volgorde en scheidt afzonderlijke getalblokken met spaties (bijvoorbeeld: „abc12def45” levert „12 45” op).

Sub ExtractAllNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim arrNums As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        arrNums = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                arrNums = arrNums & Mid(txt, i, 1)
            Else
                If arrNums <> "" Then
                    arrNums = arrNums & " "
                End If
            End If
        Next i
        
        arrNums = WorksheetFunction.Trim(arrNums)
        cell.Offset(0, 1).Value = arrNums
    Next cell
End Sub

2. Klik in de VBA-editor op de knop Uitvoeren of druk op F5 om de code uit te voeren. Er verschijnt een dialoogvenster waarin u het doelbereik kunt selecteren. De code schrijft het geëxtraheerde getal of de getallen in de kolom direct rechts van de oorspronkelijke gegevens.

Zorg ervoor dat uw werkblad de kolommen direct rechts van uw gegevens nog niet gebruikt, zodat u geen bestaande gegevens overschrijft. Als de code niet lijkt te werken, controleer dan of macro’s zijn ingeschakeld voor uw werkmap en of uw tekstwaarden daadwerkelijk getallen bevatten.

De ingebouwde functie Snelvullen van Excel haalt automatisch getallen uit tekstreeksen door te leren van voorbeeldpatronen. Deze methode is vooral handig voor snelle, handmatige bewerkingen op goed gestructureerde gegevens of wanneer u een eenvoudige oplossing zonder formules zoekt die geen complexe logica vereist.

Zo gebruikt u Snelvullen om getallen uit een tekstreeks te extraheren:

1. Typ handmatig de gewenste uitvoer voor de eerste cel in een Lege kolom naast uw gegevens. Als A2 bijvoorbeeld "OrderA12B3" bevat, typt u "123" in B2 om het extractieresultaat te demonstreren.
2. Ga naar de cel onder uw voorbeeld (bijv. B3) en begin met het typen van het verwachte resultaat voor de volgende rij. Excel toont mogelijk een voorbeeldweergave van het patroon dat het herkent. Als de voorvertoning overeenkomt met wat u bedoelt, drukt u eenvoudigweg op Enter. Zo niet, dan kunt u het gewenste patroon verder aangeven door nog enkele waarden in te voeren.
3. Nadat u het voorbeeld in de eerste cel heeft ingevoerd, selecteert u het volledige beoogde Plaatsingsgebied en gaat u naar Gegevens > Snelsplitsen(of drukt u op)Ctrl + E). Excel vult de geëxtraheerde getallen automatisch in op basis van het geleerde patroon.

Snelvullen herkent consistente relaties tussen invoer en uitvoer; zorg er daarom voor dat uw voorbeelden duidelijk en nauwkeurig zijn. Deze functie werkt het beste bij regelmatige gegevensindelingen. Als uw gegevensstructuur variabeler is of als u nauwkeurige controle nodig hebt, overweeg dan een oplossing met formules of macro’s.

Als Snelvullen niet verschijnt, controleer dan of deze optie is ingeschakeld via Bestand > Opties > Geavanceerd > Bewerkopties: zorg ervoor dat Automatisch Snelvullen is aangevinkt.