Excel-tutorial – Tekst extraheren of getal extraheren op basis van specifieke positie

In veel gevallen hebt u alleen de nuttige inhoud nodig uit een zin of tekstreeks in een cel – bijvoorbeeld de provincie uit een adres, het e-mailadres uit een zin of het pakketnummer uit een gesprek. Deze tutorial richt zich op extractie op een specifieke positie in een cel en biedt verschillende methoden om tekst of getallen uit een cel te halen op basis van die positie in Excel.

Inhoudsopgave:[ Verbergen ]

(Klik op een kop in de inhoudsopgave hieronder of rechts om naar het betreffende hoofdstuk te navigeren.)

Tekst extraheren op basis van positie

1. Nummers extraheren tekens van links of rechts

1,1 De eerste of laatste N tekens extraheren met formules

1,2 De eerste of laatste N tekens extraheren met een geweldige tool

2. Tekst extraheren vóór of na een bepaald teken/woord

2,1 Tekst extraheren vóór of na het eerste scheidingsteken (een teken)

2,1.1 Tekst extraheren vóór het eerste scheidingsteken met formules

2,1.2 Tekst extraheren na het eerste scheidingsteken met een formule

2,1.3 Tekst extraheren vóór of na het eerste scheidingsteken met een geweldig hulpmiddel

2,2 Tekst extraheren vóór of na het laatste scheidingsteken (een teken)

2,2.1 Tekst extraheren vóór het laatste scheidingsteken met een formule

2,2.2 Tekst extraheren na het laatste scheidingsteken met een formule

2,3 Tekst extraheren na het n-de teken

2,4 Haal het n-de woord uit een tekstreeks

2,4.1 Haal het n-de woord met een formule

2,4.2 Haal het n-de woord met een door de gebruiker gedefinieerde functie

2,4.3 Haal het n-de woord met een geweldig hulpmiddel

2,5 Tekst extraheren vóór of na de Voorkomen van een scheidingsteken

2,5.1 Tekst extraheren vóór de Voorkomen van een scheidingsteken

2,5.2 Tekst extraheren na de Voorkomen van een scheidingsteken

2,6 Tekst extraheren vóór of na een regeleinde

2,6.1 Tekst extraheren vóór het eerste regeleinde met een formule

2,6.2 Tekst extraheren na het laatste regeleinde met een formule

2,7 Tekst extraheren vóór of na een woord

2,7.1 Tekst extraheren vóór een bepaald woord met een formule

2,7.2 Tekst extraheren na een bepaald woord met een formule

2,7.3 Tekst extraheren vóór of na een bepaald woord met een geweldig hulpmiddel

3. Haal tussen tekens/woorden

3,1 Tekst extraheren tussen twee tekens

3,1.1 Tekst extraheren tussen twee identieke tekens met een formule

3,1.2 Tekst extraheren tussen twee verschillende tekens met een formule

3,1.3 Tekst extraheren tussen twee tekens met een geweldig hulpmiddel

3,1.4 Tekst extraheren tussen twee tekens (inclusief de tekens) volgens regel

3,2 Tekst extraheren tussen twee woorden

3,2.1 Tekst extraheren tussen twee woorden met een formule

3,2.2 Tekst extraheren tussen twee woorden met een geweldig hulpmiddel

Getallen extraheren op basis van positie

1. Haal een getal vanaf het begin van een tekstreeks

2. Haal een getal vanaf het einde van een tekstreeks

3. Haal alle getallen uit een willekeurige positie in een tekstreeks

3,1 Haal alle getallen uit een willekeurige positie in een tekstreeks met een formule

3,2 Haal alle getallen uit een willekeurige positie in een tekstreeks met VBA

3,3 Haal alle getallen uit een willekeurige positie in een tekstreeks met een geweldig hulpmiddel

4. Haal een getal na een specifieke tekst

4,1 Haal een getal na een specifieke tekst met een formule

4,2 Haal een getal na een specifieke tekst met een door de gebruiker gedefinieerde functie

Tekst extraheren op basis van positie

Dit gedeelte brengt veelvoorkomende scenario’s in kaart waarin tekst uit een cel kan worden geëxtraheerd en biedt daarbij duidelijke, stapsgewijze methoden. Klik voor meer details.

1. Nummers extraheren tekens vanaf links of rechts

Om tekens vanaf de linkerkant of rechterkant van een tekstreeks te extraheren met Nummers, kunt u een van de onderstaande methoden proberen.

1,1 De eerste of laatste N tekens extraheren met formules

Stel dat u in kolom B een lijst met tekstreeksen hebt, zoals in de onderstaande afbeelding wordt weergegeven. Om uit elke reeks de eerste twee en de laatste twee tekens te extraheren, past u de volgende formules toe.

Extraheren van Eerste N tekens uit een tekstreeks

De functie LINKS haalt eenvoudig de eerste N tekens uit een tekstreeks in Excel.

Algemene formule

=LEFT(text_string,[num_chars])

Argumenten

„Tekstreeks”: De tekstreeks die de tekens bevat die u wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„Aantal_tekens”: Geeft het aantal tekens aan dat u wilt extraheren.

Het aantal_tekens moet Groter dan of gelijk aan nul zijn;

Als het opgegeven aantal_tekens groter is dan de lengte van de tekstreeks, wordt de volledige tekst geretourneerd;

Als het aantal_tekens wordt weggelaten, wordt ervan uitgegaan dat dit 1 is.

U kunt deze formule nu gebruiken om de eerste twee tekens uit de cellen in kolom B te halen.

1. Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om het eerste resultaat te krijgen. Sleep vervolgens de automatische vulgreep van de resultaatcel naar beneden om de formule op andere cellen toe te passen.

=LEFT(B5,2)

U hebt nu de eerste twee tekens uit elke cel in het opgegeven bereik geëxtraheerd.

Extraheren van Laatste N tekens uit een tekstreeks

Hier gebruiken we de functie RECHTS om de laatste N tekens uit een tekstreeks in Excel te halen.

Algemene formule

=RIGHT(text_string,[num_chars])

Argumenten

„Tekstreeks”: De tekstreeks die de tekens bevat die u wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„Aantal_tekens”: Geeft het aantal tekens aan dat u wilt extraheren.

Het „Aantal_tekens” moet Groter dan of gelijk aan nul zijn;

Als het opgegeven „Aantal_tekens” groter is dan de lengte van de tekstreeks, wordt de volledige tekst geretourneerd;

Als het „Aantal_tekens” wordt weggelaten, wordt ervan uitgegaan dat dit 1 is.

Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep vervolgens de automatische vulgreep van deze resultaatcel naar beneden om de overige resultaten te verkrijgen.

=RIGHT(B5,2)

1,2 De eerste of laatste N tekens extraheren met een handige tool

Hoewel de bovenstaande formules eenvoudig zijn, moet u bij het extraheren van de eerste of laatste n tekens uit een lange lijst met tekstreeksen nog steeds de automatische vulgreep van boven naar beneden gebruiken, wat enigszins tijdrovend kan zijn. Daarom raden wij de Kutools voor Excel-functie "Tekst extraheren" aan om bulksgewijs de eerste of laatste N tekens uit een lijst met tekstreeksen te halen.

Kutools voor Excelbiedt meer dan 300 geavanceerde functies om complexe taken te stroomlijnen, waardoor creativiteit en efficiëntie worden verhoogd.Geïntegreerd met AI-mogelijkheden, automatiseert Kutools taken met precisie, zodat databeheer moeiteloos verloopt.Gedetailleerde informatie over Kutools voor Excel... Gratis proefversie...

1. Selecteer van tevoren de lijst met tekstreeksen waaruit u tekst wilt extraheren en klik op **Kutools** > **Tekst** > **Tekst extraheren**.

2. Configureer in het verschijnende dialoogvenster 'Tekst extraheren' de volgende instellingen.

2,1) Zorg ervoor dat u op het tabblad „Extraheren op basis van positie” blijft;

2,2) In het vak „Bereik" wordt Bereik selecteren weergegeven; u kunt dit naar behoefte wijzigen in een ander bereik;

2,3) In de sectie „Opties":

Als u Eerste N tekens wilt extraheren, kiest u de optie „De eerste N tekens” Keuzerondje en geeft u vervolgens het aantal tekens dat u wilt extraheren in het tekstvak op. In dit geval voer ik het getal 2 in;

Als u Laatste N tekens wilt extraheren, selecteert u de optie „De laatste N tekens” Keuzerondje en geeft u vervolgens het aantal tekens dat u wilt extraheren in het tekstvak op. In dit geval wil ik de laatste 2 tekens uit de tekstreeks extraheren, dus voer ik het getal 2 in het tekstvak in.

2,4) Klik op „OK”. Zie schermafbeelding:

Opmerking: om het resultaat dynamisch te laten meeveranderen met de tekstreeks, vinkt u het vakje ‘Invoegen als formule’ aan.

3. Kies in het dialoogvenster **Tekst extraheren** dat daarna verschijnt een cel om de geëxtraheerde tekens in te plaatsen en klik op **OK**.

Vervolgens worden de eerste N of laatste N tekens in één keer uit de geselecteerde cellen gehaald.

Klik hier voor meer informatie over deze functie.

Kutools voor Excel– Geef Excel een boost met meer dan 300 essentiële hulpmiddelen, waardoor uw werk sneller en eenvoudiger verloopt, en profiteer van AI-functies voor slimmere gegevensverwerking en productiviteit.Nu verkrijgen

2. Tekst extraheren vóór of na een specifiek teken of woord

Om tekst te extraheren vóór of na een bepaald teken of woord, voldoen de verschillende scenario’s in dit gedeelte perfect aan uw behoeften.

2,1 Tekst extraheren vóór of na het eerste scheidingsteken (een teken)

Zoals in de onderstaande afbeelding te zien is, kunt u een van de onderstaande methoden gebruiken om tekst vóór of na het eerste scheidingsteken uit elke cel in het bereik B4:B10 te extraheren.

2,1.1 Tekst extraheren vóór het eerste scheidingsteken met een formule

Een formule met de functies LINKS en ZOEKEN helpt u om tekst vóór het eerste scheidingsteken uit een cel te extraheren. Volg de onderstaande stappen om dit uit te voeren.

Algemene formule

=LEFT(text_string,FIND(„delimiter",text_string,1)-1)

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de subreeks wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„Scheidingsteken”: Het eerste scheidingsteken dat bepaalt welke tekst uit een cel wordt geëxtraheerd.

Selecteer een lege cel, kopieer of typ de onderstaande formule erin en druk op Enter om het eerste resultaat te krijgen. Selecteer vervolgens de cel met dit resultaat en sleep de automatische vulgreep naar beneden om de teksten vóór het eerste scheidingsteken uit de overige cellen te halen.

=LEFT(B5,FIND("-",B5,1)-1)

2,1.2 Tekst extraheren na het eerste scheidingsteken met een formule

Met de onderstaande formule extrahert u tekst na het eerste scheidingsteken uit een cel in Excel.

Algemene formule

=MID(text_string,FIND(„delimiter",text_string)+1,LEN(text_string))

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de subreeks wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„Scheidingsteken:” Het eerste scheidingsteken dat bepaalt welke tekst uit een cel wordt geëxtraheerd.

Selecteer een lege cel, kopieer of typ de onderstaande formule erin en druk op de „Enter”-toets om het eerste resultaat te verkrijgen. Selecteer de cel met het eerste resultaat en sleep de automatische invulgreep naar beneden om de overige resultaten te verkrijgen.

=MID(B5,FIND("-",B5)+1,LEN(B5))

2,1.3 Tekst extraheren vóór of na het eerste scheidingsteken met een handige tool

Wij raden de functie **'Tekst extraheren'** van **Kutools voor Excel** ten zeerste aan. Hiermee haalt u in één keer eenvoudig tekst vóór of na het eerste scheidingsteken uit een heel bereik cellen.

1. Selecteer het celbereik waaruit u de tekst wilt extraheren en klik op **Kutools** > **Tekst** > **Tekst extraheren**.

2. Configureer in het dialoogvenster 'Tekst extraheren' de volgende instellingen.

2,1) Blijf op het tabblad „Extraheren op basis van positie”;

2,2) In het vak „Bereik” wordt het Bereik selecteren weergegeven; u kunt dit naar wens wijzigen;

2,3) In het gedeelte „Opties”:

Selecteer de optie „vóór de tekst” Keuzerondje om Tekst extraheren vóór het eerste scheidingsteken, en typ vervolgens het eerste scheidingsteken in het tekstvak;

Selecteer de optie „na de tekst” Keuzerondje om Tekst extraheren na het eerste scheidingsteken, en voer vervolgens het eerste scheidingsteken in het tekstvak in.

2,4) Klik op „OK”.

Opmerking: om het resultaat dynamisch te laten meeveranderen met de tekstreeks, vinkt u het vakje ‘Invoegen als formule’ aan.

3. Vervolgens verschijnt het dialoogvenster 'Tekst extraheren'. Kies een cel om de resultaten in te plaatsen en klik op 'OK'.

Vervolgens worden de teksten vóór of na het eerste scheidingsteken in één keer uit de geselecteerde cellen gehaald.

Meer informatie over deze functie vindt u op:Snel bepaalde tekst uit cellen extraheren in Excel.

2,2 Tekst extraheren vóór of na het laatste scheidingsteken (een teken)

In de bovenstaande stappen hebben we geleerd hoe u tekst kunt extraheren vóór of na het eerste scheidingsteken in een cel. Zoals in de onderstaande afbeelding wordt weergegeven, toont dit gedeelte u twee formules om tekst te extraheren vóór of na het laatste scheidingsteken in een cel. Volg de onderstaande stappen om dit uit te voeren.

2,2.1 Tekst extraheren vóór het laatste scheidingsteken met een formule

Om tekst vóór het laatste scheidingsteken uit een cel te extraheren, combineert u de functies ZOEKEN, LENGTE en VERVANGEN binnen de functie LINKS.

Algemene formule

=LEFT(text_string,SEARCH(„#",SUBSTITUTE(text_string,"delimiter",„#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",„"))))-1)

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de subreeks wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„Scheidingsteken:” Het laatste scheidingsteken dat bepaalt welke tekst uit een cel wordt geëxtraheerd.

Selecteer een cel, typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Selecteer vervolgens deze resultaatcel en sleep de automatische vulgreep naar beneden om de teksten uit de overige tekstreeksen in dezelfde kolom te extraheren.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2,2.2 Tekst extraheren na het laatste scheidingsteken met een formule

Nadat u de tekst vóór het laatste scheidingsteken uit een cel hebt geëxtraheerd, past u de onderstaande formule toe om de resterende tekst na dat scheidingsteken te verkrijgen.

Algemene formule

=RIGHT(text_string,LEN(text_string)-SEARCH(„#",SUBSTITUTE(text_string,"delimiter",„#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",„")))))

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de subreeks wilt extraheren. Dit kan een celverwijzing zijn of een letterlijke tekstreeks tussen dubbele aanhalingstekens.

„Scheidingsteken:” Het laatste scheidingsteken dat bepaalt welk deel van de celinhoud wordt geëxtraheerd.

Selecteer een cel, typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Selecteer vervolgens de resultaatcel en sleep de automatische vulgreep naar beneden om de teksten uit de overige tekstreeksen in dezelfde kolom te extraheren.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2,3 Tekst extraheren na het n-de teken

Bekijk het onderstaande voorbeeld: in het bereik B4:B10 staat een lijst met tekstreeksen. Om de tekst na het derde teken uit elke cel te extraheren, past u een formule toe op basis van de functies MID en LENGTE.

Een schermafbeelding met een voorbeeld van het extraheren van tekst na het n-de teken in Excel

Algemene formule

=MID(text_string,nth_char+1,LEN(text_string))

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de subreeks wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„nth_teken”: Een getal dat het n-de teken aanduidt; u extrahert de tekst erna.

Selecteer een lege cel, kopieer of typ de onderstaande formule erin en druk op Enter om het resultaat te krijgen. Selecteer vervolgens deze resultaatcel en sleep de automatische vulgreep naar beneden om de resterende resultaten te verkrijgen.

=MID(B5,3+1,LEN(B5))

Een schermafbeelding met de formule voor het extraheren van tekst na het n-de teken in Excel

2,4 Het n-de woord uit een tekstreeks extraheren

Stel dat u een lijst met tekstreeksen hebt, zoals weergegeven in de onderstaande schermafbeelding, en u wilt alleen het n-de woord uit elke tekstreeks extraheren. Deze sectie biedt u drie methoden om dat te doen.

2,4.1 Haal het n-de woord uit met een formule

Combineer de functies TRIM, MID, SUBSTITUTE, REPT en LEN om het n-de woord uit een tekstreeks in een cel te halen.

Algemene formule

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(„ ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Argumenten

„Tekstreeks”: De tekstreeks waaruit u het n-de woord wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„N”: Een getal dat het n-de woord aanduidt dat u uit de tekstreeks wilt extraheren.

In dit geval bevat het bereik B5:B10 de tekstreeksen en D5:D10 de getallen die aangeven welk n-de woord moet worden opgehaald. Laten we deze formule toepassen om het n-de woord uit elke tekstreeks te extraheren.

Een schermafbeelding met de formule voor het extraheren van het n-de woord uit een tekstreeks in Excel

Selecteer een lege cel, kopieer of typ de onderstaande formule erin en druk op Enter om het eerste resultaat te krijgen. Selecteer vervolgens deze resultaatcel en sleep de automatische vulgreep naar beneden om het n-de woord uit andere cellen te verkrijgen.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Een schermafbeelding met de formule voor het extraheren van het n-de woord uit een tekstreeks in Excel met AutoAanvullen

Opmerking: u kunt het n-de getal direct in de formule typen, zoals hieronder.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2,4.2 Haal het n-de woord uit met een door de gebruiker gedefinieerde functie

Naast bovenstaande formule kunt u ook een door de gebruiker gedefinieerde functie gebruiken om het n-de woord uit een cel in Excel te halen.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het venster Microsoft Visual Basic for Applications op Invoegen > Module en plak vervolgens de onderstaande VBA-code in het codevenster.

VBA-code: haal het n-de woord uit een tekstreeks in een cel

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

Een schermafbeelding van het Microsoft Visual Basic for Applications-venster met de VBA-code voor het extraheren van het n-de woord uit een tekstreeks

3. Druk op Alt + Q om het venster Microsoft Visual Basic for Applications te sluiten.

4. Ga terug naar het werkblad met de tekstreeksen waaruit u het n-de woord wilt extraheren. Selecteer een lege cel, voer de onderstaande formule in of kopieer deze en druk op Enter om het n-de woord te verkrijgen.

=FindWord(B5,D5)

=FindWord(B5,2)

Opmerking: In de formule is D5 de cel die het getal bevat dat het n-de woord aangeeft. U kunt de celverwijzing ook direct vervangen door een getal.

5. Selecteer de resultaatcel en sleep de automatische vulgreep naar beneden om het n-de woord uit de tekstreeksen in andere cellen te extraheren.

Een schermafbeelding van het resultaat in Excel na gebruik van een door de gebruiker gedefinieerde functie om het n-de woord uit een tekstreeks te extraheren

2,4.3 Haal het n-de woord uit met een geweldige tool

Als u geen formule of door de gebruiker gedefinieerde functie handmatig wilt toepassen zoals hierboven beschreven, raden we u de functie „Haal het N-de woord uit een cel” van Kutools voor Excel aan. Met slechts enkele klikken haalt u eenvoudig het n-de woord uit een tekstreeks in een cel.

1. Selecteer een cel voor het resultaat en klik op Kutools > „Formulehulp” > Tekst > „Haal het n-de woord uit een cel”. Zie de schermafbeelding:

Een schermafbeelding van de Kutools for Excel-interface met de optie 'Haal het n-de woord in cel' onder Formulehulp

2. Configureer het dialoogvenster „Formulehulp” als volgt.

2,1) In het vak „Selecteer een formule-lijst” is de optie „Haal het N-de woord uit een cel” gemarkeerd;

2,2) In het vak „Cel” kiest u een cel met de tekstreeks waaruit u het n-de woord wilt extraheren;

2,3) In het vak „Het N-de” selecteert u een cel met het n-de getal of voert u rechtstreeks een getal in naar behoefte;

2,4) Klik op „OK.”

Een schermafbeelding van het dialoogvenster Formulehulp, ingesteld om het n-de woord uit een tekstreeks te extraheren

3. Vervolgens wordt het n-de (tweede) woord uit de tekstreeks in cel B5 geëxtraheerd, terwijl tegelijkertijd automatisch een formule wordt gegenereerd. Selecteer deze resultaatcel en sleep de automatische vulgreep naar beneden om ook uit andere tekstreeksen het n-de woord te halen.

Een schermafbeelding met het resultaat na gebruik van Kutools om het n-de woord uit een tekstreeks te extraheren

Klik hier voor meer informatie over deze functie.

2,5 Tekst extraheren vóór of na de Voorkomen van een scheidingsteken

Stel dat u een lijst met tekstreeksen hebt, zoals weergegeven in de onderstaande schermafbeelding. Om de tekst vóór of na de tweede spatie te extraheren, biedt deze sectie twee handige formules die u daarbij ondersteunen.

Een schermafbeelding met een lijst van tekstreeksen in Excel waarbij de tweede spatie wordt gebruikt voor extractie

2,5.1 Tekst extraheren vóór de Voorkomen van een scheidingsteken

U kunt de functie LINKS combineren met VERVANGEN en VIND.ALLES om tekst vóór het eerste voorkomen van een scheidingsteken uit een cel in Excel te extraheren.

Algemene formule

=LEFT(SUBSTITUTE(text_string,„delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,„delimiter",CHAR(9),n),1)-1)

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de tekst wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„N”: Een getal dat het Voorkomen van een scheidingsteken aanduidt waarnaar u Tekst extraheren wilt.

Selecteer een cel, kopieer of typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Selecteer vervolgens de resultaatcel en sleep de automatische vulgreep naar beneden om de overige resultaten in de lijst te genereren.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Een schermafbeelding van de Excel-formule die wordt gebruikt om tekst vóór de n-de spatie te extraheren

Opmerking: in de formule is B5 de cel met de tekstreeks waaruit u de tekst wilt extraheren; „ ” staat voor een spatie en het getal 2 verwijst naar de tweede spatie. U kunt deze waarden eenvoudig aanpassen aan uw eigen behoeften.

2,5.2 Tekst extraheren na de Voorkomen van een scheidingsteken

Om tekst te extraheren na het voorkomen van een scheidingsteken, combineert u de RECHTS-functie met de functies VERVANGEN, LENGTE en VIND.ALLES.

Algemene formule

=RIGHT(SUBSTITUTE(text_string, „delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, „delimiter", CHAR(9), n), 1) + 1)

Argumenten

„Tekstreeks”: De tekstreeks waaruit u de tekst wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

„N”: Een getal dat het Voorkomen van een scheidingsteken aanduidt waarna u Tekst extraheren wilt.

U kunt nu deze formule gebruiken om de tekst na de tweede spatie uit elke cel in het bereik B5:B10 te halen, als volgt.

Selecteer een cel, typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep vervolgens de automatische vulgreep van deze resultaatcel naar beneden om de overige resultaten te genereren.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

Een schermafbeelding van de Excel-formule die wordt gebruikt om tekst na de n-de spatie te extraheren

2,6 Tekst extraheren vóór of na een regeleinde

Stel dat u een bestellijst hebt in kolom B en u alleen het datumgedeelte en het productnummergedeelte uit elke cel wilt halen. Gebruik dan de onderstaande Excel-formules.

Een schermafbeelding met een bestellijst in Excel waaruit tekst vóór of na een regeleinde moet worden geëxtraheerd

2,6.1 Tekst extraheren vóór het eerste regeleinde met formule

Zoals u in de bovenstaande schermafbeelding ziet, staat het datumgedeelte vóór het eerste regeleinde in de cel. In deze sectie leest u hoe u met de functie LINKS in combinatie met ZOEKEN de tekst vóór dat eerste regeleinde kunt extraheren.

Algemene formule

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Argumenten

„Cel”: De cel waaruit u de tekst vóór de eerste regeleinde wilt extraheren.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Vervolgens ziet u dat de tekst vóór het eerste regeleinde in elke cel binnen het bereik B5:B8 wordt geëxtraheerd, zoals te zien is in de schermafbeelding hieronder.

Een schermafbeelding met het resultaat van het extraheren van tekst vóór het eerste regeleinde in Excel-cellen

Opmerking: in de formule staat TEKEN(10) voor een regeleinde in Windows.

2,6.2 Tekst extraheren na het laatste regeleinde met formule

In de vorige stap hebben we besproken hoe u tekst kunt extraheren vóór het eerste regeleinde binnen een cel. In dit gedeelte leggen we uit hoe u tekst na het laatste regeleinde binnen een cel kunt extraheren met een andere formule.

Algemene formule

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(„ ",200)),200))

Argumenten

„Cel”: De cel waaruit u de tekst vóór het eerste regeleinde wilt extraheren.

Selecteer een lege cel, typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep daarna de automatische vulgreep van deze resultaatcel naar beneden om de formule op andere cellen toe te passen.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Een schermafbeelding met het resultaat van het extraheren van tekst na het laatste regeleinde in Excel-cellen

Vervolgens wordt het productnummergedeelte uit elke cel in de lijst geëxtraheerd, zoals te zien is in de bovenstaande schermafbeelding.

Opmerking: In de formule staat CHAR(10) voor een regeleinde in Windows.

2,7 Tekst extraheren vóór of na een woord

In de vorige secties hebben we geleerd hoe u tekst kunt extraheren vóór of na een teken of scheidingsteken. Maar hoe pakt u het aan als u tekst wilt extraheren vóór of na een volledig woord? Deze sectie presenteert drie methoden om u bij deze taak te ondersteunen.

Een schermafbeelding met het extraheren van tekst vóór en na het woord 'Excel' in Excel-cellen

2,7.1 Tekst extraheren vóór een bepaald woord met formule

De volgende formule helpt u tekst te extraheren vóór een bepaald woord in een Excel-cel.

Algemene formule

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Argumenten

„Cel:” De cel waaruit u de tekst vóór een bepaald woord wilt extraheren.

„Woord:” Het woord waarvoor u alle tekst ervoor wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

In dit voorbeeld extraheren we alle teksten vóór het woord „Excel”, dus typen we het woord rechtstreeks in de formule en plaatsen het tussen dubbele aanhalingstekens. U kunt ook verwijzen naar een cel die het woord „Excel” bevat.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Een schermafbeelding met het resultaat van het extraheren van tekst vóór het woord 'Excel' in Excel-cellen

Opmerkingen:

1) Deze formule is hoofdlettergevoelig.

2) Als het door u ingevoerde woord meerdere keren voorkomt in dezelfde cel, extrahert de formule alleen de tekst na de eerste keer dat het woord voorkomt.

2,7.2 Tekst extraheren na een bepaald woord met formule

Om tekst te extraheren na een bepaald woord, kunt u de volgende formule gebruiken.

Algemene formule

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Argumenten

„Cel:” De cel waaruit u de tekst na een bepaald woord wilt extraheren.

„Woord:” Het woord waarna u alle tekst erna wilt extraheren. Dit kan Een celverwijzing zijn of een daadwerkelijke tekstreeks tussen dubbele aanhalingstekens;

Selecteer een cel, typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep daarna de automatische vulgreep vanuit de resultaatcel naar beneden om de formule op andere cellen toe te passen.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Vervolgens ziet u dat alle teksten na het woord „Excel” in elke cel worden geëxtraheerd, zoals te zien is in de onderstaande schermafbeelding.

Een schermafbeelding met het resultaat van het extraheren van tekst na het woord 'Excel' in Excel-cellen

Opmerkingen:

1) Deze formule is niet hoofdlettergevoelig.

2) Als het door u ingevoerde woord meerdere keren voorkomt in dezelfde cel, extrahert de formule alleen de tekst na de eerste keer dat het woord voorkomt.

2,7.3 Tekst extraheren vóór of na een bepaald woord met een geweldige tool

Als u formules lastig vindt, raden we u de functie „Tekst extraheren” van „Kutools voor Excel” sterk aan. Met slechts enkele klikken automatiseert u daarmee uw extractietaak in Excel.

1. Klik op Kutools > Tekst > „Tekst extraheren” om de functie te activeren.

Een schermafbeelding van de optie 'Tekst extraheren' op het Kutools-tabblad in het lint van Excel

2. Voer in het dialoogvenster „Tekst extraheren” de volgende instellingen in.

2,1) Zorg ervoor dat u zich bevindt op het tabblad „Extraheren op basis van positie”;

2,2) Klik in het vak „Bereik” op de Knop voor bereikselectie

knop om het bereik van cellen te selecteren waaruit u Tekst extraheren wilt;

2,3) In de sectie „Opties”:

Kies de optie „vóór de tekst” Keuzerondje om alle teksten vóór een woord te extraheren en voer vervolgens het woord in het tekstvak in;

Kies de optie „na de tekst” Keuzerondje om alle teksten na een woord te extraheren en voer vervolgens het woord in het tekstvak in.

2,4) Klik op de knop „OK”. Zie schermafbeelding:

Een schermafbeelding van het dialoogvenster Tekst extraheren

Opmerking: als u dynamische resultaten wilt, schakelt u het selectievakje „Invoegen als formule” in – de resultaten worden dan automatisch bijgewerkt zodra de gegevens in het bereik wijzigen.

3. Vervolgens verschijnt het dialoogvenster „Tekst extraheren”. Selecteer een cel voor het resultaat en klik op OK.

Een schermafbeelding van het dialoogvenster Tekst extraheren met geselecteerde uitvoercel

Vervolgens worden de teksten vóór of na een bepaald woord binnen elke cel in het geselecteerde bereik direct geëxtraheerd.

Opmerking: deze functie is hoofdlettergevoelig.

Klik hier voor meer informatie over deze functie.

3. Extraheren tussen tekens of woorden

Als u tekst wilt extraheren tussen bepaalde tekens of woorden, probeer dan de volgende methoden.

3,1 Tekst extraheren tussen twee tekens

Om tekst te extraheren tussen twee tekens – of deze nu hetzelfde zijn of verschillend – biedt deze sectie diverse methoden; kies de methode die het beste aansluit bij uw behoeften.

3,1.1 Tekst extraheren tussen twee identieke tekens met een formule

Zoals in de onderstaande afbeelding te zien is, bevat kolom B een lijst met tekstreeksen, en u wilt het gedeelte tussen de „/”-tekens uit elke cel in het bereik Nummers extraheren. De volgende formule helpt u daarbij.

Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep vervolgens de AutoAanvulgreep vanuit de resultaatcel naar beneden om resultaten voor andere cellen in de lijst te verkrijgen.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Vervolgens wordt de tekst tussen de twee identieke „/”-tekens uit elke cel in het bereik geëxtraheerd. Zie de afbeelding:

Opmerking:

1) „B5” is de cel waaruit u Tekst extraheren tussen twee identieke tekens wilt;

2) „/” zijn de twee identieke tekens waartussen u Tekst extraheren wilt.

U dient deze variabelen aan te passen op basis van uw eigen gegevens.

3,1.2 Tekst extraheren tussen twee verschillende tekens met een formule

Nadat u hebt geleerd hoe u tekst extraheren tussen twee identieke tekens binnen een cel, tonen we hier een formule om tekst te extraheren tussen twee verschillende tekens. Zoals in de onderstaande afbeelding te zien is, wilt u alleen het e-mailadres tussen de „” uit elke cel in kolom B extraheren. U kunt als volgt te werk gaan.

Algemene formule

=MID(LEFT(cel,FIND(„end_char",cell)-1),FIND(„start_char",cell)+1,LEN(cell))

Argumenten

„Cel:” De cel waaruit u de tekst tussen twee verschillende tekens wilt extraheren;

„Eindteken:” Het eindteken van de twee verschillende tekens;

„Startteken:” Het startteken van de twee verschillende tekens.

Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Sleep daarna de AutoAanvulgreep van deze resultaatcel naar beneden om de formule op andere cellen toe te passen.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

U ziet dat alleen de tekst tussen de opgegeven tekens is geëxtraheerd, zoals in de afbeelding hierboven te zien is.

3,1.3 Tekst extraheren tussen twee tekens met een geweldige tool

Hier raden we u sterk aan de functie „Haal de string tussen opgegeven tekens uit” van Kutools voor Excel te gebruiken, zodat u eenvoudig tekst kunt extraheren tussen twee identieke of verschillende tekens binnen een cel in Excel.

1. Selecteer een lege cel voor het resultaat en klik op „Kutools” > „Formulehulp” > „Formulehulp”.

2. Voer in het dialoogvenster „Formulehulp” de volgende instellingen in.

2,1) Vink het vakje „Filter” aan en voer vervolgens het woord „„Extract”” in het tekstvak in;

2,2) Klik in het vak „Selecteer een formule-lijst” op de optie „Haal de string tussen opgegeven tekens uit”;

2,3) In de sectie „Argumentinvoer”:

Selecteer in het vak „Cel” een cel waaruit u de tekst wilt extraheren (hier selecteer ik cel B5);

Voer in het vak „Startteken(s” het startteken van de twee verschillende tekens in;)

Voer in het vak „Eind”-teken(s) het eindteken van de twee verschillende tekens in.

2,4) Klik op „Ok.” Zie schermafbeelding:

3. Vervolgens wordt alleen de tekst tussen „” in cel B5 geëxtraheerd. Er is tegelijkertijd een formule aangemaakt; selecteer deze resultaatcel en sleep de AutoAanvulgreep naar beneden om tekst uit andere cellen in dezelfde lijst te extraheren.

Klik hier voor meer informatie over deze functie.

3,1.4 Tekst extraheren tussen twee tekens (inclusief de tekens) volgens regel

Als u de twee tekens na het extraheren wilt behouden, past u een regel toe in de functie ‘Tekst extraheren’ van Kutools voor Excel.

1. Klik op „Kutools” > „Tekst” > „Tekst extraheren”.

2. Voer in het dialoogvenster „Tekst extraheren” de volgende instellingen in.

2,1) Klik op het tabblad „Extraheren op basis van regel”;

2,2) Klik in de sectie „Bereik” op de

knop om het bereik van cellen te selecteren waaruit u de tekst tussen tekens wilt extraheren;

2,3) Voer in het vak „Tekst” „""” in;

Tip: „""” zijn de tekens waartussen u Tekst extraheren wilt, * is een jokerteken dat een willekeurig aantal tekens vertegenwoordigt. U kunt de voorwaarde naar behoefte invoeren.

2,4) Klik op de knop „Toevoegen” om de voorwaarde toe te voegen aan het vak „Regelinhoud”-lijst;

2,5) Klik op de knop „Ok”. Zie schermafbeelding:

Een schermafbeelding met de instellingen in het dialoogvenster Tekst extraheren, inclusief bereikselectie en tekstinvoer

3. Er verschijnt een nieuw dialoogvenster ‘Tekst extraheren’. Selecteer een cel om het resultaat weer te geven en klik op ‘OK’.

Een schermafbeelding met het tweede dialoogvenster Tekst extraheren met een geselecteerde uitvoercel

Vervolgens wordt de tekst tussen de opgegeven tekens (inclusief die tekens) in één keer geëxtraheerd uit elke cel in het geselecteerde bereik.

Een schermafbeelding met de geëxtraheerde tekst tussen opgegeven tekens in Excel met behulp van Kutools

3,2 Tekst extraheren tussen twee woorden

Naast het extraheren van tekst tussen twee tekens, moet u mogelijk ook tekst extraheren tussen twee woorden. Bijvoorbeeld: alle tekstreeksen tussen de woorden „KTE” en „feature” uit elke cel in kolom B halen, zoals te zien is in de onderstaande afbeelding. U kunt een van de volgende methoden proberen om dit voor elkaar te krijgen.

Een schermafbeelding met een lijst van tekstreeksen tussen de woorden 'KTE' en 'feature' in kolom B voor extractie

3,2.1 Tekst extraheren tussen twee woorden met een formule

Gebruik een formule met de functies MID en ZOEKEN om alle tekstreeksen tussen twee woorden in een cel te extraheren.

Algemene formule

=MID(cell,SEARCH(„start_word",cell)+3,SEARCH(„end_word",cell)-SEARCH(„start_word",cell)-4)

Argumenten

„Cel:” de cel waaruit u alle tekstreeksen tussen twee woorden wilt extraheren;

„Startwoord:” het startwoord van de twee woorden waarna u alle tekstreeksen wilt extraheren;

„Lengte1:” de tekenlengte van het startwoord.

„Eindwoord:” het eindwoord van de twee woorden waarvoor u alle tekstreeksen wilt extraheren.

„Lengte2:” de tekenlengte van het startwoord plus 1.

Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om het resultaat te krijgen. Selecteer vervolgens de resultaatcel en sleep de AutoAanvulgreep naar beneden om de formule op andere cellen toe te passen.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Een schermafbeelding met de formule voor het extraheren van tekst tussen twee woorden in Excel met behulp van de functies MID en ZOEKEN

Opmerking: In de formule staat het getal 3 voor de lengte van het woord „KTE”; het getal 4 staat voor de lengte van het woord „KTE” plus 1.

U ziet dat alle tekstfragmenten tussen de opgegeven twee woorden zijn geëxtraheerd uit elke cel in kolom B.

3,2.2 Tekst extraheren tussen twee woorden met een geweldige tool

Voor veel Excel-gebruikers zijn formules lastig te onthouden en toe te passen. Met de functie „Haal de string tussen opgegeven tekens uit” van Kutools voor Excel extrahereert u in een handomdraai tekst tussen twee woorden – met slechts een paar klikken.

1. Selecteer een cel voor het resultaat en klik vervolgens op „Kutools” > „Formulehulp” > „Formulehulp”.

Een schermafbeelding met de optie Formulehulp op het Kutools-tabblad in het lint van Excel

2. Configureer het dialoogvenster „Formulehulp” als volgt.

2,1) Vink het vakje „Filter” aan en voer vervolgens het woord „„Extract”” in het tekstvak in;

2,2) Klik in het vak „Selecteer een formule-lijst” op de optie „Haal de string tussen opgegeven tekens uit”;

2,3) In de sectie „Argumentinvoer”:

Selecteer in het vak „Cel” een cel waaruit u de tekst wilt extraheren (hier selecteer ik cel B5);

Voer in het vak „Startteken(s” het startwoord van de twee woorden in waarna u alle tekstreeksen wilt extraheren;)

Voer in het vak „Eindteken(s” het eindwoord van de twee woorden in waarvoor u alle tekstreeksen wilt extraheren.)

2,4) Klik op „Ok.” Zie schermafbeelding:

Een schermafbeelding van het dialoogvenster Formulehulp met de optie 'Tekenreeksen extraheren tussen opgegeven tekst' geselecteerd

3. Vervolgens worden alle tekstfragmenten tussen de woorden „KTE” en „feature” in cel B5 geëxtraheerd. Tegelijkertijd wordt een formule gegenereerd: selecteer de resultaatcel en sleep de AutoAanvulgreep naar beneden om tekst uit andere cellen in dezelfde lijst te extraheren.

Een schermafbeelding van geëxtraheerde tekst tussen twee woorden in een Excel-cel, met de toegepaste formule

Getallen extraheren op basis van positie

Voor een lijst met alfanumerieke reeksen zijn er drie mogelijke situaties:

Het getal staat aan het begin van de tekst;
Het getal staat aan het einde van de tekst;
Het getal kan zich op elke plek in de tekst bevinden.

In deze sectie bieden we diverse methoden om nummers te extraheren voor elk van de hierboven genoemde gevallen.

1 Getal extraheren vanaf het begin van een tekenreeks

Dit gedeelte presenteert een formule die u helpt om alleen de getallen te extraheren die vóór tekst in een cel staan.

Algemene formule

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Argumenten

„Cel:” De cel waaruit u het getal links van de tekstreeks wilt extraheren.

Opmerking:

1) Als u Excel 2019 of ouder gebruikt, moet u de toetsencombinatie „Ctrl” + „Shift” + „Enter” indrukken om deze matrixformule te bevestigen.

2) Als u Excel 365 of Excel 2021 gebruikt, hoeft u deze formule alleen te bevestigen met de toets „Enter”.

Selecteer een lege cel, voer de onderstaande formule in en druk op Ctrl + Shift + Enter of gewoon op Enter om het resultaat te krijgen. Selecteer vervolgens de resultaatcel en sleep de AutoAanvulgreep naar beneden om getallen uit andere cellen te verkrijgen.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Opmerkingen:

1) Als een cel alleen cijfers bevat, wordt het volledige getal geëxtraheerd.

2) Deze formule Nummers extraheren alleen aan de linkerkant van een tekstreeks. Als er cijfers in het midden of aan het einde van de tekstreeks staan, worden deze genegeerd.

2 Getal extraheren vanaf het einde van een tekenreeks

Zoals in de onderstaande afbeelding te zien is, kunt u de volgende formule gebruiken om alleen de getallen na de tekst in een cel te extraheren.

Algemene formule

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Argumenten

„Cel:” De cel waaruit u het getal aan het begin van de tekstreeks wilt extraheren.

Opmerking:

1) Als u Excel 2019 of een eerdere versie gebruikt, moet u op de toetsen "Ctrl" + "Shift" + „Enter" drukken om deze matrixformule te bevestigen.

2) Als u Excel 365 of Excel 2021 gebruikt, hoeft u deze formule alleen met de Enter-toets te bevestigen.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Opmerkingen:

1) Als een cel alleen cijfers bevat, wordt het volledige getal geëxtraheerd.

2) Deze formule Nummers extraheren alleen vanaf de rechterkant van een tekstreeks. Als er cijfers in het midden of aan het begin van de tekstreeks staan, worden deze genegeerd.

3. Alle getallen extraheren uit willekeurige posities in een tekenreeks

De bovenstaande methoden helpen u alleen getallen te extraheren vanaf het begin of einde van een tekenreeks. Als u alle getallen uit een willekeurige positie in een tekenreeks wilt extraheren, bieden we hier drie methoden om dat te realiseren.

3,1 Alle getallen extraheren vanuit willekeurige posities in een tekenreeks met een formule

U kunt de volgende formule gebruiken om alle cijfers op willekeurige posities in een tekenreeks in Excel te extraheren.

1. Selecteer een lege cel, kopieer of typ de onderstaande formule en druk op Enter om alle getallen uit cel B5 te halen.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Selecteer de resultaatcel en sleep de AutoAanvulgreep naar beneden om automatisch alle getallen uit de andere cellen over te nemen.

3,2 Alle getallen extraheren vanuit willekeurige posities in een tekenreeks met VBA

De bovenstaande formule is voor veel Excel-gebruikers te lang en te ingewikkeld. Gelukkig kunt u deze taak eenvoudig automatiseren met een VBA-script in Excel. Volg hiervoor de onderstaande stappen.

1. Druk op de toetsencombinatie Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.

2. Klik in het venster „Microsoft Visual Basic for Applications” op „Invoegen” > „Module” en plak de onderstaande VBA-code in het modulecodevenster.

VBA-code: Alle getallen extraheren vanuit willekeurige posities in een tekenreeks

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    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

3. Druk op F5 om de code uit te voeren. Selecteer in het dialoogvenster „Kutools for Excel” het celbereik waaruit u alle getallen uit elke cel wilt extraheren en klik op „OK”.

Een schermafbeelding van het Kutools for Excel-dialoogvenster dat vraagt om het invoerbereik

4. Vervolgens verschijnt er nog een dialoogvenster „Kutools for Excel”. Selecteer in dit venster een doelcel en klik op „OK”.

Een schermafbeelding van het Kutools for Excel-dialoogvenster dat vraagt om het uitvoerbereik

Vervolgens worden alle getallen in één keer geëxtraheerd uit elke cel in het geselecteerde bereik.

Een schermafbeelding met het resultaat na het extraheren van alle getallen met VBA

4. Getallen extraheren na een specifieke tekst

Zoals in de onderstaande afbeelding te zien is, biedt deze sectie twee methoden om getallen na de tekst „Nr.” te extraheren.

4,1 Getallen extraheren na een specifieke tekst met een formule

U kunt de volgende formule gebruiken om getallen na een specifieke tekst in een Excel-cel te extraheren.

Algemene formule:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&„ "))),{2,3,4,5,6}))

Argumenten

„Cel:” De cel waaruit u getallen na een specifieke tekst wilt extraheren;

„Tekst:” De tekst waarna u getallen wilt extraheren.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Opmerkingen:

1) Als een cel de specifieke tekst niet bevat, geeft de formule #N/B als resultaat.

2) De formule is hoofdlettergevoelig.

4,2 Getallen extraheren na een specifieke tekst met een door de gebruiker gedefinieerde functie

De volgende door de gebruiker gedefinieerde functie helpt u ook bij het extraheren van getallen die na een specifieke tekst in een cel staan. Volg deze stappen.

1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.

2. Klik in het venster „Microsoft Visual Basic for Applications” op „Invoegen” > „Module” en plak de onderstaande VBA-code vervolgens in het modulecodevenster.

VBA-code: getallen extraheren na een specifieke tekst in een cel

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

Een schermafbeelding van de VBA-code om getallen na specifieke tekst in Excel te extraheren

3. Druk op Alt + Q om het venster Microsoft Visual Basic for Applications te sluiten.

4. Selecteer een cel, voer de onderstaande formule in en druk op Enter. Sleep daarna de AutoAanvulgreep vanuit deze resultaatcel naar beneden om de formule op andere cellen toe te passen.

=GetNumberAfterTheChar(B5,"No. ")

Een schermafbeelding met het resultaat van de VBA-methode om getallen na specifieke tekst in Excel te extraheren

Opmerkingen:

1) In deze formule is B5 de cel waarin u Nummers extraheren, en „No. ” is de specifieke tekst waarna u Nummers extraheren. U kunt deze naar wens aanpassen.

2) Als er een scheidingsteken tussen de specifieke tekst en het getal staat, voegt u het scheidingsteken toe aan het einde van de tekst. In dit geval heb ik een spatie toegevoegd na de tekst „No.”, zodat deze uiteindelijk wordt weergegeven als „No. ”.

3) Deze methode is hoofdletterongevoelig;

4) Als een cel de specifieke tekst niet bevat, geeft de formule een leeg resultaat.

Gerelateerde artikelen:

Excel-tutorial: Tekst-, getal- en datumcellen splitsen (in meerdere kolommen verdelen)
Deze tutorial is onderverdeeld in drie delen: het splitsen van tekstcellen, getalcellen en datumcellen. Elk deel bevat praktische voorbeelden om u te helpen dit soort problemen vlot op te lossen.
Klik voor meer informatie...

Voeg tekst en cijfers in Excel toe op een opgegeven positie in een cel
In Excel komt het vaak voor dat u tekst of getallen aan cellen moet toevoegen—bijvoorbeeld spaties tussen namen invoegen, een voor- of achtervoegsel toevoegen, of streepjes in socialezekerheidsnummers plaatsen. Deze tutorial behandelt vrijwel alle scenario’s waarin u iets aan cellen moet toevoegen in Excel, inclusief de bijbehorende methoden.
Klik voor meer informatie...

Verwijder specifieke tekens, woorden of getallen uit tekstreeksen in Excel
Stel dat u een lange lijst met tekstreeksen hebt die ongewenste tekens, getallen of symbolen bevatten. Soms wilt u tekens verwijderen op basis van hun positie – bijvoorbeeld vanaf het begin, het einde of ergens in het midden van de tekst – of juist bepaalde woorden of cijfers uit de hele lijst verwijderen. Het zoeken naar oplossingen, één voor één, kan behoorlijk tijdrovend en frustrerend zijn. Deze tutorial bundelt diverse efficiënte methoden om tekens, woorden of getallen in Excel eenvoudig te verwijderen.
Klik voor meer informatie...

Beste Office-productiviteitshulpmiddelen

🤖	KUTOOLS AI Assistant: Revolutioneer Data-analyse op basis van:Intelligente uitvoering \| Genereer code\| Maak aangepaste formules \| Analyseer gegevens en genereer grafieken\| Roep Verbeterde functies aan…
	Populaire functies:Zoeken, markeren of Dubbele waarden markeren \| Verwijder lege rijen \| Kolommen samenvoegen of cellen zonder gegevensverlies \| Afronden zonder formule...
	Super ZOEKEN:VLookup met meerdere criteria \| VLookup met meerdere waarden \| VLookup over meerdere werkbladen \| Fuzzy Match....
	Geavanceerde keuzelijst:Snel een keuzelijst maken \| Afhankelijke keuzelijst \| Keuzelijst met meervoudige selectie....
	Kolombeheerder:Voeg een specifiek aantal kolommen toe\|Verplaats kolommen\|Wissel zichtbaarheidsstatus van verborgen kolommen\|Vergelijk bereiken en kolommen...
	Uitgelichte functies:Rasterfocus \| Ontwerpweergave \|Verbeterde formulebalk \| Werkmap- en bladbeheerder \| Bronnenbibliotheek(Automatische tekst)\| Datumkiezer \| Werkbladen samenvoegen \| Versleutelen/Cellen decoderen \| E-mails verzenden op basis van lijst \| Superfilter \| Speciaal filter(Filter cellen met vetgedrukt lettertype/cursief/doorgestreept...) ...
	Top 15 gereedschapssets:12 Teksthulpmiddelen(Tekst toevoegen,Specifieke tekens verwijderen, ...)\| 50+Grafiektypen(Gantt-diagram, ...)\| 40+ Praktische formules(Leeftijd berekenen op basis van geboortedatum, ...)\| 19 Invoeghulpmiddelen(QR-code Invoegen,Afbeelding invoegen vanaf pad, ...)\| 12 Conversiehulpmiddelen(Omzetten naar woorden,Wisselkoersconversie, ...)\| 7 Samenvoegen en splitsenhulpmiddelen(Geavanceerd samenvoegen van rijen,Cellen splitsen, ...)\|... en meer

Gebruik Kutools in uw voorkeurstaal – ondersteunt Engels, Spaans, Duits, Frans, Chinees en 40+ andere talen!

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...

Meer details over Kutools voor Excel...Gratis downloaden...

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!

Meer details over Office Tab...Gratis downloaden...

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

Meer details over Kutools for Office...Gratis downloaden...

🤖	KUTOOLS AI Assistant: Revolutioneer Data-analyse op basis van:Intelligente uitvoering \| Genereer code\| Maak aangepaste formules \| Analyseer gegevens en genereer grafieken\| Roep Verbeterde functies aan…
	Populaire functies:Zoeken, markeren of Dubbele waarden markeren \| Verwijder lege rijen \| Kolommen samenvoegen of cellen zonder gegevensverlies \| Afronden zonder formule...
	Super ZOEKEN:VLookup met meerdere criteria \| VLookup met meerdere waarden \| VLookup over meerdere werkbladen \| Fuzzy Match....
	Geavanceerde keuzelijst:Snel een keuzelijst maken \| Afhankelijke keuzelijst \| Keuzelijst met meervoudige selectie....
	Kolombeheerder:Voeg een specifiek aantal kolommen toe\|Verplaats kolommen\|Wissel zichtbaarheidsstatus van verborgen kolommen\|Vergelijk bereiken en kolommen...
	Uitgelichte functies:Rasterfocus \| Ontwerpweergave \|Verbeterde formulebalk \| Werkmap- en bladbeheerder \| Bronnenbibliotheek(Automatische tekst)\| Datumkiezer \| Werkbladen samenvoegen \| Versleutelen/Cellen decoderen \| E-mails verzenden op basis van lijst \| Superfilter \| Speciaal filter(Filter cellen met vetgedrukt lettertype/cursief/doorgestreept...) ...
	Top 15 gereedschapssets:12 Teksthulpmiddelen(Tekst toevoegen,Specifieke tekens verwijderen, ...)\| 50+Grafiektypen(Gantt-diagram, ...)\| 40+ Praktische formules(Leeftijd berekenen op basis van geboortedatum, ...)\| 19 Invoeghulpmiddelen(QR-code Invoegen,Afbeelding invoegen vanaf pad, ...)\| 12 Conversiehulpmiddelen(Omzetten naar woorden,Wisselkoersconversie, ...)\| 7 Samenvoegen en splitsenhulpmiddelen(Geavanceerd samenvoegen van rijen,Cellen splitsen, ...)\|... en meer