Excel-handleiding – tekst of getal extraheren op basis van specifieke positie

In veel gevallen wilt u alleen de relevante tekst uit een zin of tekenreeks in een cel extraheren, zoals het extraheren van de provincie uit een adres, het e-mailadres uit een zin, of het pakketnummer uit een gesprek. Deze handleiding richt zich op het extraheren op basis van een specifieke positie in een cel en verzamelt verschillende methoden om tekst of getallen uit een cel te halen op basis van positie in Excel.

Tekst extraheren op basis van positie

In dit onderdeel worden veelvoorkomende posities verzameld waaruit tekst uit een cel kan worden gehaald, met bijbehorende methoden stap voor stap uitgelegd. U kunt verder bladeren voor meer details.

1. Extraheer het aantal tekens vanaf links of rechts

Om het aantal tekens vanaf de linker- of rechterkant van een tekenreeks te extraheren, kunt u een van de onderstaande methoden proberen.

1.1 Extraheer de eerste of laatste N tekens met formules

Stel dat u een lijst met tekstreeksen in kolom B heeft, zoals hieronder weergegeven. Om de eerste2 tekens en de laatste2 tekens uit elke tekenreeks te halen, kunt u de volgende formules gebruiken.

De eerste N tekens uit een tekstreeks extraheren

Met de functie LINKS kunt u eenvoudig de eerste N tekens uit een tekstreeks in Excel halen.

Algemene formule

=LEFT(text_string,[num_chars])

Argumenten

Nu kunt u deze formule toepassen om de eerste2 tekens uit de cellen in kolom B te halen.

1. Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het eerste resultaat te krijgen. Selecteer de resultaatcel en sleep de vulgreep naar beneden om de formule toe te passen op andere cellen.

=LEFT(B5,2)

Nu zijn de eerste2 tekens uit elke cel in het opgegeven bereik gehaald.

De laatste N tekens uit een tekstreeks extraheren

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

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer vervolgens deze resultaatcel en sleep de vulgreep naar beneden om andere resultaten te krijgen.

=RIGHT(B5,2)

1.2 Extraheer de eerste of laatste N tekens met een krachtige tool

Hoewel 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 vulgreep van boven naar beneden slepen, wat tijdrovend kan zijn. Daarom raden we de "Extract Text"-functie van Kutools voor Excel aan om in bulk de eerste of laatste N tekens uit een lijst met tekstreeksen te halen.

1. Selecteer vooraf de lijst met tekstreeksen waaruit u tekst wilt extraheren en klik op "Kutools" > "Tekst" > "Extract Text".

2. In het pop-upvenster "Extract Text" moet u het volgende instellen.

Opmerking: Om het resultaat dynamisch te maken wanneer de tekstreeks verandert, kunt u het vakje "Invoegen als formule" aanvinken.

3. In het volgende pop-upvenster "Extract Text" kiest u een cel om de geëxtraheerde tekens weer te geven en klikt u op "OK".

Daarna worden de eerste N of laatste N tekens in bulk uit de geselecteerde cellen gehaald.

Klik voor meer informatie over deze functie.

2. Extraheer tekst vóór of na een bepaald teken/woord

Om tekst vóór of na een bepaald teken of woord te extraheren, bieden de verschillende scenario's in dit onderdeel uitkomst.

2.1 Extraheer tekst vóór of na de eerste scheidingsteken (een teken)

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

2.1.1 Extraheer tekst vóór de eerste scheidingsteken met formule

Een formule gebaseerd op de functies LINKS en VIND.SPEC kan helpen om tekst vóór de eerste scheidingsteken uit een cel te halen. Volg onderstaande stappen om dit te doen.

Algemene formule

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

Argumenten

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het eerste resultaat te krijgen. Selecteer de eerste resultaatcel en sleep de vulgreep naar beneden om de teksten vóór de eerste scheidingsteken van andere cellen te halen.

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

2.1.2 Extraheer tekst na de eerste scheidingsteken met formule

De onderstaande formule helpt om tekst na de eerste scheidingsteken uit een cel in Excel te halen.

Algemene formule

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

Argumenten

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

2.1.3 Extraheer tekst vóór of na de eerste scheidingsteken met een krachtige tool

Hier raden we de functie "Extract Text" van Kutools voor Excel sterk aan. Met deze functie kunt u eenvoudig teksten vóór of na de eerste scheidingsteken uit een bereik van cellen in bulk halen.

1. Selecteer het bereik cellen waaruit u de tekst wilt extraheren en klik vervolgens op "Kutools" > "Tekst" > "Extract Text".

2. In het dialoogvenster "Extract Text" moet u het volgende instellen.

Opmerking: Om het resultaat dynamisch te maken wanneer de tekstreeks verandert, kunt u het vakje Invoegen als formule aanvinken.

3. Vervolgens verschijnt er een ander dialoogvenster "Extract Text". Kies een cel om de resultaten weer te geven en klik op "OK."

Daarna worden teksten vóór of na de eerste scheidingsteken direct uit de geselecteerde cellen gehaald.

Voor meer informatie over deze functie, zie: Snel bepaalde tekst uit cellen halen in Excel.

2.2 Extraheer tekst vóór of na de laatste scheidingsteken (een teken)

In de bovenstaande stappen hebben we geleerd hoe je tekst vóór of na de eerste scheidingsteken uit een cel haalt. Zoals te zien is in de screenshot hieronder, laat dit onderdeel twee formules zien om tekst vóór of na de laatste scheidingsteken uit een cel te halen. Volg de onderstaande stappen om dit te doen.

2.2.1 Extraheer tekst vóór de laatste scheidingsteken met formule

Om tekst vóór de laatste scheidingsteken uit een cel te halen, kunt u de functies ZOEKEN, LENGTE en SUBSTITUEREN gebruiken binnen de functie LINKS.

Algemene formule

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

Argumenten

Selecteer een cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om de teksten uit andere tekstreeksen in dezelfde kolom te halen.

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

2.2.2 Extraheer tekst na de laatste scheidingsteken met formule

Nadat u tekst vóór de laatste scheidingsteken uit een cel heeft gehaald, kunt u onderstaande formule toepassen om de tekst na de laatste scheidingsteken te halen.

Algemene formule

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

Argumenten

Selecteer een cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om de teksten uit andere tekstreeksen in dezelfde kolom te halen.

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

2.3 Extraheer tekst na het n-de teken

Bekijk het onderstaande voorbeeld: er is een lijst met tekstreeksen in het bereik B4:B10. Om de tekst na het derde teken uit elke cel te halen, kunt u een formule gebruiken op basis van de functies DEEL en LENGTE.

Algemene formule

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

Argumenten

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om andere resultaten te krijgen.

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

2.4 Extraheer het n-de woord uit een tekst

Stel dat u een lijst met tekstreeksen heeft zoals hieronder weergegeven en u wilt alleen het n-de woord uit de tekstreeks halen. Dit onderdeel biedt drie methoden om dit te doen.

2.4.1 Extraheer het n-de woord met formule

U kunt de functies SPATIES.WEGHALEN, DEEL, SUBSTITUEREN, HERHALEN en LENGTE combineren 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

In dit geval bevat het bereik B5:B10 de tekstreeksen, D5:D10 bevat de getallen die het n-de woord aangeven. Laten we deze formule toepassen om het n-de woord uit de tekstreeks te halen.

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het eerste resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om het n-de woord van andere cellen te halen.

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

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

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

2.4.2 Extraheer het n-de woord met gebruikersgedefinieerde functie

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

1. Druk op de toetsen "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 kopieer vervolgens de onderstaande VBA in het codevenster.

VBA-code: Het n-de woord uit een tekstreeks in een cel extraheren

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

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

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

=FindWord(B5,D5)

Of

=FindWord(B5,2)

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

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

2.4.3 Extraheer het n-de woord met een krachtige tool

Als u niet handmatig een formule of gebruikersgedefinieerde functie wilt toepassen, raden we de functie "Het N-de woord uit een cel extraheren" van Kutools voor Excel aan. Met deze functie kunt u eenvoudig het n-de woord uit een tekstreeks in een cel halen met slechts enkele klikken.

1. Selecteer een cel om het resultaat te plaatsen en klik op "Kutools" > "Formulehulp" > "Tekst" > "Het N-de woord uit een cel extraheren". Zie screenshot:

2. In het dialoogvenster "Formulehulp" moet u het volgende instellen.

3. Vervolgens wordt het n-de (tweede) woord uit de tekstreeks in cel B5 gehaald en ziet u dat er een formule is aangemaakt. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om het n-de woord uit andere tekstreeksen te halen.

Klik voor meer informatie over deze functie.

2.5 Extraheer tekst vóór of na het n-de voorkomen van een scheidingsteken

Stel dat u een lijst met tekstreeksen heeft zoals hieronder weergegeven. Om de tekst vóór of na het tweede voorkomen van een spatie te halen, biedt dit onderdeel twee formules om u te helpen.

2.5.1 Extraheer tekst vóór het n-de voorkomen van een scheidingsteken

U kunt de functie LINKS samen met de functies SUBSTITUEREN en VIND.SPEC gebruiken om tekst vóór het n-de voorkomen van een scheidingsteken uit een cel in Excel te halen.

Algemene formule

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

Argumenten

Selecteer een cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om andere resultaten in de lijst te krijgen.

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

Opmerking: In de formule is B5 de cel met de tekstreeks waaruit u de tekst wilt extraheren; “ ” staat voor een spatie en het getal2 voor het tweede voorkomen van een spatie. U kunt deze naar wens aanpassen.

2.5.2 Extraheer tekst na het n-de voorkomen van een scheidingsteken

Om tekst na het n-de voorkomen van een scheidingsteken te halen, kunt u de functie RECHTS gebruiken samen met de functies SUBSTITUEREN, LENGTE en VIND.SPEC.

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

Nu kunt u deze formule toepassen om de tekst na het tweede voorkomen van de spatie uit elke cel in het bereik B5:B10 te halen, zoals hieronder.

Selecteer een cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om andere resultaten te krijgen.

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

2.6 Extraheer tekst vóór of na een regeleinde

Stel dat u een bestellijst in kolom B heeft en u wilt alleen het datumgedeelte en het productnummer uit elke cel halen. U kunt onderstaande Excel-formules gebruiken om dit te doen.

2.6.1 Extraheer tekst vóór het eerste regeleinde met formule

Zoals te zien is in de bovenstaande screenshot, staat het datumgedeelte vóór het eerste regeleinde in de cel. Dit onderdeel laat zien hoe u met de functie LINKS samen met ZOEKEN tekst vóór het eerste regeleinde uit een cel haalt.

Algemene formule

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

Argumenten

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

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

Nu ziet u dat de tekst vóór het eerste regeleinde in elke cel in het bereik B5:B8 is gehaald, zoals in de screenshot hieronder.

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

2.6.2 Extraheer tekst na het laatste regeleinde met formule

In de vorige stap hebben we besproken hoe u tekst vóór het eerste regeleinde uit een cel haalt. In dit deel laten we zien hoe u tekst na het laatste regeleinde uit een cel haalt met een andere formule.

Algemene formule

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

Argumenten

Selecteer een lege cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om de formule op andere cellen toe te passen.

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

Daarna wordt het productnummer uit elke cel in de lijst gehaald, zoals hierboven weergegeven.

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

2.7 Extraheer tekst vóór of na een woord

In de vorige onderdelen hebben we geleerd hoe je tekst vóór of na een teken of scheidingsteken haalt. Maar hoe haal je tekst vóór of na een heel woord? Dit onderdeel introduceert drie methoden om deze taak uit te voeren.

2.7.1 Extraheer tekst vóór een bepaald woord met formule

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

Algemene formule

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

Argumenten

Selecteer een lege cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

In dit voorbeeld halen we alle tekst vóór het woord “Excel”, dus typen we het woord direct in de formule en zetten het tussen dubbele aanhalingstekens. U kunt ook verwijzen naar een cel die het woord “Excel” bevat.

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

Opmerkingen:

2.7.2 Extraheer tekst na een bepaald woord met formule

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

Algemene formule

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

Argumenten

Selecteer een cel, voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer de resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

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

Daarna ziet u dat alle tekst na het woord “Excel” in elke cel is gehaald, zoals in de screenshot hieronder.

Opmerkingen:

2.7.3 Extraheer tekst vóór of na een bepaald woord met een krachtige tool

Als u het gebruik van formules omslachtig vindt, raden we de functie "Extract Text" van Kutools voor Excel aan. Met deze functie automatiseert u het extractieproces in Excel met slechts enkele klikken.

1. Klik op "Kutools" > "Tekst" > "Extract Text" om deze functie te activeren.

2. Stel het volgende in het dialoogvenster "Extract Text" in.

2.1) Zorg dat u zich in het tabblad "Extract by location" bevindt;

2.2) Klik in het vak "Bereik" op de knop om het bereik cellen te selecteren waaruit u tekst wilt extraheren;

2.3) In het gedeelte "Opties":

Om alle tekst vóór een woord te extraheren, kiest u de optie "Tekst voor" en voert u het woord in het tekstvak in;

Om alle tekst na een woord te extraheren, kiest u de optie "Tekst na" en voert u het woord in het tekstvak in.

2.4) Klik op de knop "OK". Zie screenshot:

Opmerkingen: Als u dynamische resultaten wilt maken, vinkt u het vakje "Invoegen als formule" aan. De resultaten worden dan automatisch bijgewerkt wanneer de gegevens in het bereik wijzigen.

3. Vervolgens verschijnt er een dialoogvenster "Extract Text". Selecteer een cel om het resultaat weer te geven en klik op de knop "OK".

Daarna wordt tekst vóór of na een bepaald woord in elke cel in het geselecteerde bereik direct geëxtraheerd.

Opmerking: Deze functie is hoofdlettergevoelig.

Klik voor meer informatie over deze functie.

3. Extraheer tussen tekens/woorden

Wilt u tekst tussen bepaalde tekens of woorden extraheren? Probeer dan de volgende methoden.

3.1 Extraheer tekst tussen twee tekens

Om tekst tussen twee tekens te extraheren, die hetzelfde of verschillend kunnen zijn. Dit onderdeel biedt verschillende methoden, zodat u kunt kiezen wat het beste bij uw situatie past.

3.1.1 Extraheer tekst tussen twee dezelfde tekens met formule

Zoals te zien is in de onderstaande screenshot, is er een lijst met tekstreeksen in kolom B en wilt u het getal tussen de tekens “/” uit elke cel in het bereik halen. De volgende formule helpt u hierbij.

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer de resultaatcel en sleep de vulgreep naar beneden om resultaten voor andere cellen in de lijst te krijgen.

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

Daarna wordt de tekst tussen twee dezelfde tekens “/” uit elke cel in het bereik gehaald. Zie screenshot:

Opmerking:

3.1.2 Extraheer tekst tussen twee verschillende tekens met formule

Na het leren extraheren van tekst tussen twee dezelfde tekens binnen een cel, laten we nu een formule zien om tekst tussen twee verschillende tekens te halen. Zoals te zien is in de onderstaande screenshot, kunt u om alleen het e-mailadres tussen “<” en “>” uit elke cel in kolom B te halen, het volgende doen.

Algemene formule

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

Argumenten

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze 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 wordt gehaald, zoals hierboven weergegeven.

3.1.3 Extraheer tekst tussen twee tekens met een krachtige tool

Hier raden we de functie "De tekst tussen opgegeven teksten extraheren" van Kutools voor Excel aan om eenvoudig tekst tussen twee dezelfde of verschillende tekens binnen een cel in Excel te halen.

1. Selecteer een lege cel om het resultaat weer te geven en klik vervolgens op "Kutools" > "Formulehulp" > "Formulehulp".

2. Stel het volgende in het dialoogvenster "Formulehulp" in.

3. Vervolgens wordt alleen de tekst tussen “<” en “>” in cel B5 gehaald. Tegelijkertijd is er een formule aangemaakt; u kunt deze resultaatcel selecteren en de vulgreep naar beneden slepen om teksten uit andere cellen in dezelfde lijst te halen.

Klik voor meer informatie over deze functie.

3.1.4 Extraheer tekst tussen twee tekens (inclusief de tekens) op basis van regel

Wilt u de twee tekens behouden na het extraheren? Probeer dan een regel toe te passen in de functie Extract Text van Kutools voor Excel.

1. Klik op "Kutools" > "Tekst" > "Extract Text".

2. Stel het volgende in het dialoogvenster "Extract Text" in.

2.1) Klik op het tabblad "Extract by rule";

2.2) Klik in het gedeelte "Bereik" op de knop om het bereik cellen te selecteren waaruit u tekst tussen tekens wilt extraheren;

2.3) Voer in het vak "Tekst" "<*>" in;

Tips: "<" en ">" zijn de tekens waartussen u tekst wilt extraheren, * is een wildcard die voor elk aantal tekens staat. U kunt de voorwaarde naar wens invoeren.

2.4) Klik op de knop "Toevoegen" om de voorwaarde toe te voegen aan de lijst "Regelinhoud";

2.5) Klik op de knop "OK". Zie screenshot:

3. Vervolgens verschijnt er een ander dialoogvenster "Extract Text". Selecteer een cel om het resultaat weer te geven en klik op de knop "OK".

Daarna wordt de tekst tussen opgegeven tekens (inclusief de tekens) in bulk uit elke cel in het geselecteerde bereik gehaald.

3.2 Extraheer tekst tussen twee woorden

Naast het extraheren van tekst tussen twee tekens, wilt u misschien ook tekst tussen twee woorden halen. Bijvoorbeeld, haal alle tekstreeksen tussen de woorden “KTE” en “feature” uit elke cel in kolom B, zoals hieronder weergegeven. U kunt een van de volgende methoden proberen.

3.2.1 Extraheer tekst tussen twee woorden met formule

U kunt een formule gebruiken op basis van de functies DEEL en ZOEKEN om alle tekstreeksen tussen twee woorden binnen een cel te halen.

Algemene formule

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

Argumenten

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

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

Opmerking: In de formule staat het getal3 voor de tekenlengte van het woord "KTE"; het getal4 staat voor de tekenlengte van het woord "KTE" plus1.

U ziet dat alle tekstreeksen tussen de opgegeven twee woorden uit elke cel in kolom B worden gehaald.

3.2.2 Extraheer tekst tussen twee woorden met een krachtige tool

Voor veel Excel-gebruikers zijn formules lastig te onthouden en te gebruiken. Met de functie "De tekst tussen opgegeven teksten extraheren" van Kutools voor Excel kunt u eenvoudig tekst tussen twee woorden halen met slechts enkele klikken.

1. Selecteer een cel om het resultaat weer te geven en klik vervolgens op "Kutools" > "Formulehulp" > "Formulehulp."

2. Stel het volgende in het dialoogvenster "Formulehulp" in.

3. Vervolgens worden alle tekstreeksen tussen de woorden “KTE” en “feature” in cel B5 gehaald. Tegelijkertijd is er een formule aangemaakt; u kunt deze resultaatcel selecteren en de vulgreep naar beneden slepen om teksten uit andere cellen in dezelfde lijst te halen.

Getallen extraheren op basis van positie

Bij een lijst met alfanumerieke tekenreeksen zijn er drie mogelijkheden:

1. Het getal staat aan het begin van de tekst;
2. Het getal staat aan het einde van de tekst;
3. Het getal kan overal in de tekst staan.

In dit onderdeel bieden we verschillende methoden om de getallen in elk van de bovengenoemde gevallen te extraheren.

1 Extraheer getal vanaf de linkerkant van een tekenreeks

In dit deel wordt een formule geïntroduceerd waarmee u alleen getallen die vóór tekst in een cel staan, kunt extraheren.

Algemene formule

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

Argumenten

Opmerking:

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

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

Opmerkingen:

2 Extraheer getal vanaf de rechterkant van een tekenreeks

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

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

Opmerking:

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

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

Opmerkingen:

3. Extraheer alle getallen vanaf elke positie in een tekstreeks

De bovenstaande methoden helpen alleen getallen vanaf de linker- of rechterkant van een tekstreeks te extraheren. Wilt u alle getallen uit een tekstreeks halen, waar ze zich ook bevinden, dan bieden we hier drie methoden om dit te doen.

3.1 Extraheer alle getallen vanaf elke plek in een tekenreeks met formule

U kunt de volgende formule toepassen om alle getallen uit een tekstreeks in Excel te halen.

1. Selecteer een lege cel, kopieer of voer de onderstaande formule in 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 vulgreep naar beneden om alle getallen uit andere cellen te halen.

3.2 Extraheer alle getallen vanaf elke plek in een tekenreeks met VBA

De bovenstaande formule is voor veel Excel-gebruikers te lang en ingewikkeld. U kunt ook een VBA-script uitvoeren om deze taak in Excel te automatiseren. Doe het volgende.

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

2. Klik in het geopende venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module". Kopieer vervolgens de onderstaande VBA in het modulecodevenster.

VBA-code: Haal alle getallen uit een tekstreeks

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 de toets "F5" om de code uit te voeren. In het geopende dialoogvenster "KutoolsforExcel" selecteert u het bereik cellen waaruit u alle getallen wilt halen en klikt u op de knop "OK".

4. Vervolgens verschijnt er een ander dialoogvenster "KutoolsforExcel". Selecteer hierin een doelcel en klik op "OK".

Daarna worden alle getallen in bulk uit elke cel in het geselecteerde bereik gehaald.

4. Extraheer getallen na specifieke tekst

Zoals te zien is in de onderstaande screenshot, kunt u om getallen na de specifieke tekst “No.” te extraheren, twee methoden gebruiken die in dit onderdeel worden uitgelegd.

4.1 Extraheer getallen na een specifieke tekst met formule

U kunt de volgende formule toepassen om getallen na een specifieke tekst in een cel in Excel te halen.

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

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op "Enter" om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

=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:

4.2 Extraheer getallen na een specifieke tekst met gebruikersgedefinieerde functie

De volgende gebruikersgedefinieerde functie kan ook helpen om getallen na een specifieke tekst in een cel te halen. Doe het volgende.

1. Druk op de toetsen "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 kopieer vervolgens de onderstaande VBA-code in het modulecodevenster.

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

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

3. Druk op de toetsen "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". Selecteer deze resultaatcel en sleep de vulgreep naar beneden om deze formule op andere cellen toe te passen.

=GetNumberAfterTheChar(B5,"No. ")

Opmerkingen:

Gerelateerde artikelen: