Excel-zelfstudie - extraheer tekst of nummer op specifieke positie

In veel gevallen hoeft u alleen de nuttige inhoud uit een zin of een tekstreeks in een cel te extraheren, zoals het extraheren van de provincie uit het adres, het extraheren van het e-mailadres uit een zin, het extraheren van het koeriersfactuurnummer uit een gesprek, enzovoorts. Deze tutorial beperkt de extractie tot een specifieke positie in een cel en verzamelt verschillende methoden om tekst of getallen uit een cel te extraheren op specifieke positie in Excel.

Deze tutorial behandelt voornamelijk: (Klik op een titel in de inhoudsopgave hieronder of aan de rechterkant om naar de bijbehorende inhoud te navigeren.)

Tekst extraheren op positie

1. Extraheer het aantal tekens van links of rechts

1.1 Extraheer de eerste of laatste N tekens met formules

1.2 Extraheer de eerste of laatste N-tekens met een geweldige tool

2. Tekst extraheren voor of na een bepaald teken/woord

2.1 Tekst extraheren voor 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 formule

2.1.3 Extraheer tekst voor of na het eerste scheidingsteken met een geweldige tool

2.2 Tekst extraheren voor of na het laatste scheidingsteken (een teken)

2.2.1 Tekst extraheren voor het laatste scheidingsteken met formule

2.2.2 Tekst extraheren na het laatste scheidingsteken met formule

2.3 Tekst extraheren na het n-de teken

2.4 Extraheer het zoveelste woord uit een tekenreeks

2.4.1 Extraheer het nde woord met formule

2.4.2 Extraheer het nde woord met de door de gebruiker gedefinieerde functie

2.4.3 Extraheer het zoveelste woord met een geweldige tool

2.5 Tekst extraheren voor of na het zoveelste voorkomen van een scheidingsteken

2.5.1 Tekst extraheren voor de zoveelste keer dat een scheidingsteken voorkomt

2.5.2 Tekst extraheren na het zoveelste voorkomen van een scheidingsteken

2.6 Tekst extraheren voor of na regeleinde

2.6.1 Tekst extraheren voor het eerste regeleinde met formule

2.6.2 Tekst extraheren na het laatste regeleinde met formule

2.7 Tekst extraheren voor of na een woord

2.7.1 Tekst extraheren voor een bepaald woord met formule

2.7.2 Tekst extraheren na een bepaald woord met formule

2.7.3 Tekst extraheren voor of na een bepaald woord met een geweldige tool

3. Extraheren tussen karakters/woorden

3.1 Extraheer tekst tussen twee tekens

3.1.1 Extraheer tekst tussen twee dezelfde tekens met formule

3.1.2 Extraheer tekst tussen twee verschillende tekens met formule

3.1.3 Extraheer tekst tussen twee karakters met een geweldige tool

3.1.4 Extraheer tekst tussen twee tekens (inclusief de tekens) per regel

3.2 Tekst extraheren tussen twee woorden

3.2.1 Tekst extraheren tussen twee woorden met formule

3.2.2 Extraheer tekst tussen twee woorden met een geweldige tool

Getallen extraheren op positie

1. Nummer links van een tekenreeks extraheren

2. Haal het nummer uit de rechterkant van een tekenreeks

3. Extraheer alle getallen vanaf elke positie in een tekenreeks

3.1 Extraheer alle getallen van overal in een string met formule

3.2 Extraheer alle getallen van overal in een string met VBA

3.3 Extraheer alle getallen van overal in een string met een geweldige tool

4. Extraheer nummer na specifieke tekst

4.1 Extraheer nummer na een specifieke tekst met formule

4.2 Nummer extraheren na een specifieke tekst met door de gebruiker gedefinieerde functie

Tekst extraheren op positie

Deze sectie verzamelt algemene standpunten waar een tekst uit een cel kan worden geëxtraheerd en biedt overeenkomstige methoden om ze stap voor stap af te handelen. U kunt bladeren voor meer details.

1. Extraheer het aantal tekens van links of rechts

Om het aantal tekens aan 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 heeft in kolom B, zoals weergegeven in de onderstaande schermafbeelding, om de eerste 2 tekens en de laatste 2 tekens uit elke reeks te extraheren, dan kunt u de volgende formules toepassen.

Extraheer de eerste N tekens uit een tekenreeks

De LEFT-functie kan helpen om eenvoudig de eerste N-tekens uit een tekstreeks in Excel te extraheren.

Generieke formule

=LEFT(text_string,[num_chars])

argumenten

Tekst reeks: de tekenreeks die de tekens bevat die u wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

Aantal_tekens: Specificeert het aantal tekens dat u wilt extraheren.

De Num_chars moeten groter zijn dan of gelijk zijn aan nul;

Als het opgegeven aantal_tekens groter is dan de lengte van de tekenreeks, wordt de hele tekst geretourneerd;

Als de Num_chars wordt weggelaten, wordt aangenomen dat het 1.

Nu kunt u deze formule toepassen om de eerste 2 tekens uit cellen in kolom B te extraheren.

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

=LEFT(B5,2)

Nu zijn de eerste 2 tekens in elke cel van het bereik B5:B10 geëxtraheerd.

Extraheer de laatste N tekens uit een tekenreeks

Hier passen we de RECHTS-functie toe om de laatste N-tekens uit een tekstreeks in Excel te extraheren.

Generieke formule

=RIGHT(text_string,[num_chars])

argumenten

Tekst reeks: de tekenreeks die de tekens bevat die u wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

Aantal_tekens: Specificeert het aantal tekens dat u wilt extraheren.

Het Aantal_tekens moet groter zijn dan of gelijk zijn aan nul;

Als de opgegeven Aantal_tekens is groter dan de lengte van de tekenreeks, het retourneert de hele tekst;

Als de Aantal_tekens wordt weggelaten, wordt aangenomen dat het 1.

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

=RIGHT(B5,2)

1.2 Extraheer de eerste of laatste N-tekens met een geweldige tool

Hoewel de bovenstaande formules eenvoudig zijn, moet u om de eerste of laatste n tekens uit een lange lijst met tekenreeksen te extraheren, nog steeds de hendel voor automatisch aanvullen van boven naar beneden slepen, wat misschien een beetje tijdrovend is. Hier beveelt aan Kutools for Excel's Extraheer tekst hulpprogramma om de eerste of laatste N-tekens in bulk uit een lijst met tekstreeksen te extraheren.

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

2. In het opduiken Extraheer tekst dialoogvenster, moet u als volgt configureren.

2.1) Zorg ervoor dat u in de verblijft Extract op locatie tab;

2.2) In de Bereik box, het geselecteerde bereik wordt binnenin weergegeven, u kunt naar een ander bereik veranderen volgens uw behoeften;

2.3) In de Opties sectie:

Als u de eerste N tekens wilt extraheren, kiest u de Het eerste N-teken keuzerondje en geef vervolgens het aantal tekens op dat u wilt extraheren in het tekstvak. In dit geval voer ik het cijfer 2 in;

Als u de laatste N tekens wilt extraheren, selecteert u de Het laatste N-teken keuzerondje en geef vervolgens het aantal tekens op dat u wilt extraheren in het tekstvak. In dit geval wil ik de laatste 2 tekens uit de tekstreeks halen, dus ik voer het nummer 2 in het tekstvak in.

2.4) Klik op OK. Zie screenshot:

Opmerking:: Om het resultaat dynamisch te maken wanneer de tekenreeks verandert, kunt u de Invoegen als formule doos.

3. In de volgende pop-up Extraheer tekst dialoogvenster, kies een cel om de geëxtraheerde tekens uit te voeren en klik vervolgens op OK.

Vervolgens worden de opgegeven eerste of laatste N-tekens in bulk uit geselecteerde cellen geëxtraheerd.

Klik om meer over deze functie te weten.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

2. Tekst extraheren voor of na een bepaald teken/woord

Om tekst voor of na een bepaald teken of woord te extraheren, zullen de verschillende scenario's in deze sectie aan uw behoeften voldoen.

2.1 Tekst extraheren voor of na het eerste scheidingsteken (een teken)

Zoals te zien is in de onderstaande schermafbeelding, kunt u een van de onderstaande methoden toepassen om tekst voor 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 formule

Door een formule toe te passen op basis van de functies LEFT en FIND, kunt u tekst vóór het eerste scheidingsteken uit een cel extraheren. U kunt de onderstaande stappen volgen om het voor elkaar te krijgen.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u de subtekenreeks wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

Delimiter: Het eerste scheidingsteken dat definieert welke tekst uit een cel wordt geëxtraheerd.

Selecteer een lege cel, kopieer of voer de onderstaande formule erin in en druk op de Enter toets om het eerste resultaat te krijgen. Selecteer de eerste resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om de teksten vóór het eerste scheidingsteken van andere cellen te krijgen.

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

2.1.2 Tekst extraheren na het eerste scheidingsteken met formule

De onderstaande formule helpt bij het extraheren van tekst na het eerste scheidingsteken uit een cel in Excel.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u de subtekenreeks wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

scheidingsteken: Het eerste scheidingsteken dat definieert welke tekst uit een cel wordt geëxtraheerd.

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

2.1.3 Extraheer tekst voor of na het eerste scheidingsteken met een geweldige tool

Hier beveelt het ten zeerste aan Extraheer tekst nut van Kutools voor Excel. Met deze functie kunt u eenvoudig teksten voor of na het eerste scheidingsteken uit een reeks cellen in bulk extraheren.

1. Selecteer het celbereik waar u de tekst wilt extraheren en klik vervolgens op Kutools > Tekst > Extraheer tekst.

2. In de Extraheer tekst dialoogvenster, moet u als volgt configureren.

2.1) Blijf in de Extract op locatie tab;

2.2) In de Bereik box, het geselecteerde bereik wordt binnenin weergegeven, u kunt het naar behoefte wijzigen;

2.3) In de Opties sectie:

Als u tekst vóór het eerste scheidingsteken wilt extraheren, selecteert u de voor de tekst keuzerondje en typ vervolgens het eerste scheidingsteken in het tekstvak;

Om tekst na het eerste scheidingsteken te extraheren, selecteert u de na de tekst keuzerondje en voer vervolgens het eerste scheidingsteken in het tekstvak in.

2.4) Klik op OK.

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

3. Dan nog een Extraheer tekst dialoogvenster verschijnt, kies een cel om de resultaten uit te voeren en klik op OK.

Vervolgens worden teksten voor of na het eerste scheidingsteken in één keer uit geselecteerde cellen geëxtraheerd.

Ga voor meer informatie over deze functie naar: Extraheer snel bepaalde tekst uit cellen in Excel.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

2.2 Tekst extraheren voor of na het laatste scheidingsteken (een teken)

In de bovenstaande stappen hebben we de methoden geleerd voor het extraheren van tekst voor of na het eerste scheidingsteken uit een cel. Zoals te zien is in de onderstaande schermafbeelding, toont deze sectie u twee formules om tekst voor of na het laatste scheidingsteken uit een cel te extraheren. U kunt de onderstaande stappen volgen om het voor elkaar te krijgen.

2.2.1 Tekst extraheren voor het laatste scheidingsteken met formule

Om tekst vóór het laatste scheidingsteken uit een cel te extraheren, kunt u de functies ZOEKEN, LEN en VERVANG gebruiken binnen de functie LINKS.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u de subtekenreeks wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

scheidingsteken: Het laatste scheidingsteken dat definieert welke tekst uit een cel wordt geëxtraheerd.

Selecteer een cel, voer de onderstaande formule in en druk op de Enter toets om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om de teksten uit andere 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 formule

Nadat u tekst vóór het laatste scheidingsteken uit een cel hebt geëxtraheerd, kunt u de onderstaande formule toepassen om de tekst na het laatste scheidingsteken naar behoefte te extraheren.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u de subtekenreeks wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

scheidingsteken: Het laatste scheidingsteken dat definieert welke tekst uit een cel wordt geëxtraheerd.

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

2.3 Tekst extraheren na het n-de teken

Kijk naar 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 extraheren, kunt u een formule toepassen op basis van de MID-functie en de LEN-functie.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u de subtekenreeks wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

nth_char: Een getal staat voor het n-de teken en u extraheert de tekst erachter.

Selecteer een lege cel, kopieer of voer de onderstaande formule erin in en druk op de Enter toets om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om andere resultaten te krijgen.

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

2.4 Extraheer het zoveelste woord uit een tekenreeks

Stel dat je een lijst met tekststrings hebt zoals getoond in de onderstaande schermafbeelding, en alleen het n-de woord uit de tekststring wilt extraheren, dan biedt deze sectie drie methoden om dit voor elkaar te krijgen.

2.4.1 Extraheer het nde woord met formule

U kunt de functies TRIM, MID, SUBSTITUTE, REPT en LEN combineren om het n-de woord uit een tekstreeks in een cel te extraheren.

Generieke formule

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

argumenten

Tekst reeks: De tekstreeks waaruit u het n-de woord wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

N: Een getal staat voor het n-de woord dat u uit de tekstreeks haalt.

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

Selecteer een lege cel, kopieer of voer de onderstaande formule erin in en druk op de Enter toets om het eerste resultaat te krijgen. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om het zoveelste woord van andere cellen te krijgen.

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

Opmerking: U kunt het n-de getal als volgt rechtstreeks in de formule typen.

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

2.4.2 Extraheer het nde woord met de door de gebruiker gedefinieerde functie

Afgezien van de bovenstaande formule, kunt u ook een door de gebruiker gedefinieerde functie toepassen om het n-de woord uit een cel in Excel te extraheren.

1. druk de anders + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. In de Microsoft Visual Basic voor toepassingen venster klikt Invoegen > module, en kopieer vervolgens de onderstaande VBA naar het codevenster.

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

Functie ExtractTheNthWord(Bron als string, positie als geheel getal) 'Update by Extendoffice 20211202 Dim arr() As String arr = VBA.Split(Bron, " ") xCount = UBound(arr) If xCount < 1 Or (Position - 1) > xCount Of Position < 0 Then FindWord = "" Else FindWord = arr( Positie - 1) End If End-functie

3. druk de anders + Q toetsen om de Microsoft Visual Basic voor toepassingen venster.

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

=FindWord(B5,D5)

=FindWord(B5,2)

Opmerking: in de formule is D5 de cel die een getal bevat dat het n-de woord vertegenwoordigt. Als alternatief kunt u de celverwijzing direct vervangen door een getal.

5. Selecteer de resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om het zoveelste woord uit de tekstreeksen van andere cellen te extraheren.

2.4.3 Extraheer het zoveelste woord met een geweldige tool

Als u een formule of een door de gebruiker gedefinieerde functie hierboven niet handmatig wilt toepassen, raadt u hier Kutools voor Excel aan Extraheer het nde woord in de cel nutsvoorziening. Met deze functie kunt u met slechts een paar klikken eenvoudig het zoveelste woord uit een tekstreeks in een cel extraheren.

1. Selecteer een cel om het resultaat te plaatsen en klik op Kutools > Formule Helper > Tekst > Extract het nde woord in cel. Zie screenshot:

2. In de Formules Helper dialoogvenster, moet u als volgt configureren.

2.1) In de Kies een formulelijst box, de Extraheer het nde woord in de cel optie is gemarkeerd;

2.2) In de Cel kies een cel die de tekstreeks bevat waaruit u het n-de woord wilt extraheren;

2.3) In de De N-de selecteer een cel met het n-de nummer of voer direct een nummer in volgens uw behoeften;

2.4) Klik op OK.

3. Vervolgens wordt het n-de (tweede) woord geëxtraheerd uit de tekstreeks in cel B5, en u kunt zien dat er tegelijkertijd een formule wordt gemaakt. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om het zoveelste woord uit andere tekstreeksen te halen.

Klik om meer over deze functie te weten.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

2.5 Tekst extraheren voor of na het zoveelste voorkomen van een scheidingsteken

Stel dat je een lijst met tekstreeksen hebt, zoals weergegeven in de onderstaande schermafbeelding. Om de tekst voor of na het tweede voorkomen van een spatie te extraheren, biedt deze sectie twee formules om u te helpen dit voor elkaar te krijgen.

2.5.1 Tekst extraheren voor de zoveelste keer dat een scheidingsteken voorkomt

U kunt de LEFT-functie samen met de SUBSTITUTE- en FIND-functies gebruiken om tekst te extraheren vóór het zoveelste voorkomen van een scheidingsteken uit een cel in Excel.

Generieke formule

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

argumenten

Tekst reeks: De tekenreeks waaruit u de tekst wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

N: Een getal staat voor de zoveelste keer dat een scheidingsteken voorkomt, waarvoor u tekst wilt extraheren.

Selecteer een cel, kopieer of voer de onderstaande formule in en druk op de Enter toets om het resultaat te krijgen. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen 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 die de tekstreeks bevat waaruit u de tekst wilt extraheren; “ ” staat hier voor een spatie en het cijfer 2 staat voor het tweede voorkomen van een spatie. U kunt ze wijzigen volgens uw behoeften.

2.5.2 Tekst extraheren na het zoveelste voorkomen van een scheidingsteken

Om tekst te extraheren na het zoveelste voorkomen van een scheidingsteken, kunt u de functie RECHTS toepassen met de functies SUBSTITUTE, LEN en FIND.

Generieke 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

Tekst reeks: De tekenreeks waaruit u de tekst wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

N: Een getal staat voor de zoveelste keer dat een scheidingsteken voorkomt, waarna u tekst extraheert.

Nu kunt u deze formule als volgt toepassen om de tekst na het tweede voorkomen van de spatie uit elke cel in het bereik B5:B10 te extraheren.

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

2.6 Tekst extraheren voor of na regeleinde

Stel u heeft een bestellijst in kolom B en u wilt uit elke cel alleen het datumgedeelte en het artikelnummergedeelte extraheren. U kunt de onderstaande Excel-formules gebruiken om dit voor elkaar te krijgen.

2.6.1 Tekst extraheren voor het eerste regeleinde met formule

Zoals je kunt zien in de bovenstaande schermafbeelding, bevindt het datumgedeelte zich vóór het eerste regeleinde in de cel. Deze sectie demonstreert een LEFT-functie samen met de SEARCH-functie om u te helpen de tekst te extraheren vóór het eerste regeleinde in een cel.

Generieke formule

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

argumenten

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

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

Vervolgens kunt u de tekst zien voordat het eerste regeleinde in elke cel in bereik B5: B8 wordt geëxtraheerd, zoals weergegeven in de onderstaande schermafbeelding.

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

2.6.2 Tekst extraheren na het laatste regeleinde met formule

In de vorige stap hebben we het gehad over het extraheren van tekst vóór het eerste regeleinde in een cel. En dit deel zal u helpen bij het extraheren van tekst na het laatste regeleinde in een cel met een andere formule.

Generieke formule

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

argumenten

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

Selecteer een lege cel, voer de onderstaande formule in en druk op de Enter toets om het resultaat te krijgen. Selecteer deze resultaatcel en sleep vervolgens de hendel voor automatisch aanvullen naar beneden om de formule op andere cellen toe te passen.

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

Dan is het productnr. een deel van elke cel in de lijst wordt geëxtraheerd zoals weergegeven in de bovenstaande schermafbeelding.

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

2.7 Tekst extraheren voor of na een woord

In de vorige delen hebben we geleerd hoe we tekst voor of na een teken of scheidingsteken kunnen extraheren. Wat moet je doen om tekst voor of na een heel woord te extraheren? In dit gedeelte worden drie methoden geïntroduceerd om u te helpen deze taak te volbrengen.

2.7.1 Tekst extraheren voor een bepaald woord met formule

Met de volgende formule kunt u tekst voor een bepaald woord in een cel in Excel extraheren.

Generieke 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 waarvan u alle tekst ervoor wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

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

In dit voorbeeld gaan we alle teksten voor het woord "Excel" extraheren, dus we typen het woord direct in de formule en plaatsen het tussen dubbele aanhalingstekens. Of u kunt verwijzen naar een cel die het woord "Excel" bevat.

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

Opmerkingen:

1) Deze formule is hoofdlettergevoelig.

2) Als het woord dat u hebt ingevoerd dubbel voorkomt in dezelfde cel, extraheert de formule de tekst pas na de eerste keer dat het voorkomt.

2.7.2 Tekst extraheren na een bepaald woord met formule

Om tekst na een bepaald woord te extraheren, kunt u de volgende formule toepassen om het voor elkaar te krijgen.

Generieke 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 waarvan u alle tekst erna wilt extraheren. Het kan een celverwijzing zijn of een daadwerkelijke tekenreeks tussen dubbele aanhalingstekens;

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

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

Vervolgens kunt u alle teksten zien nadat het woord "Excel" in elke cel is geëxtraheerd, zoals weergegeven in de onderstaande schermafbeelding.

Opmerkingen:

1) Deze formule is niet hoofdlettergevoelig.

2) Als het woord dat u hebt ingevoerd dubbel voorkomt in dezelfde cel, extraheert de formule de tekst pas na de eerste keer dat het voorkomt.

2.7.3 Tekst extraheren voor of na een bepaald woord met een geweldige tool

Als u denkt dat het gebruik van de formule veel ongemak kan veroorzaken, raadt u hier ten zeerste de Extraheer tekst nut van Kutools for Excel. Deze functie helpt om de extractietaak in Excel met slechts een paar klikken te automatiseren.

1. klikken Kutools > Tekst > Extraheer tekst om deze functie in te schakelen.

2. In de Extraheer tekst dialoogvenster, voer dan de volgende instellingen uit.

2.1) Zorg ervoor dat u zich in de Extract op locatie tab;

2.2) In de Bereik vak, klik op het

knop om het cellenbereik te selecteren waar u teksten wilt extraheren;

2.3) In de Opties sectie:

Als u alle teksten vóór een woord wilt extraheren, kiest u de voor de tekst keuzerondje en voer vervolgens het woord in het tekstvak in;

Als u alle teksten na een woord wilt extraheren, kiest u de na de tekst keuzerondje en typ het woord in het tekstvak.

2.4) Klik op de OK knop. Zie screenshot:

Opmerkingen: Als u dynamische resultaten wilt creëren, vink dan de Invoegen als formule doos. Vervolgens worden de resultaten automatisch bijgewerkt wanneer de gegevens in het bereik veranderen.

3. Vervolgens een Extraheer tekst dialoogvenster verschijnt, moet u een cel selecteren om het resultaat uit te voeren en vervolgens op de OK knop.

Vervolgens worden teksten voor of na een bepaald woord binnen elke cel in het geselecteerde bereik onmiddellijk geëxtraheerd.

Opmerking: Deze functie is hoofdlettergevoelig.

Klik om meer over deze functie te weten.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

3. Extraheren tussen karakters/woorden

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

3.1 Extraheer tekst tussen twee tekens

Om tekst tussen twee tekens te extraheren, die dezelfde of verschillende tekens kunnen zijn. Dit gedeelte biedt verschillende methoden en u kunt er een kiezen op basis van uw behoeften.

3.1.1 Extraheer tekst tussen twee dezelfde tekens met formule

Zoals te zien is in de onderstaande schermafbeelding, is er een lijst met tekstreeksen in kolom B en wilt u het nummergedeelte tussen de tekens "/" uit elke cel in het bereik extraheren, de volgende formule kan u een plezier doen.

Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op de Enter toets om het resultaat te krijgen. Selecteer de resultaatcel en sleep deze vervolgens Handgreep voor automatisch aanvullen naar beneden om de resultaten van andere cellen in de lijst te krijgen.

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

Vervolgens wordt de tekst tussen twee dezelfde tekens "/" geëxtraheerd uit elke cel in het bereik. Zie schermafdruk:

Opmerking:

1) B5 is de cel waaruit u tekst tussen twee dezelfde tekens wilt extraheren;

2) "/” zijn twee dezelfde tekens waarvan u tekst ertussen wilt extraheren.

U moet deze variabelen wijzigen op basis van uw eigen gegevens.

3.1.2 Extraheer tekst tussen twee verschillende tekens met formule

Nadat we hebben geleerd hoe u tekst tussen twee dezelfde tekens in een cel kunt extraheren, zullen we hier een formule demonstreren om tekst tussen twee verschillende tekens te extraheren. Zoals te zien is in de onderstaande schermafbeelding, kunt u het volgende doen om alleen het e-mailadres tussen de "<" en ">" uit elke cel in kolom B te extraheren.

Generieke 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;

End_char: Het eindkarakter van de twee verschillende karakters;

Start_char: Het startkarakter van de twee verschillende karakters.

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

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

U kunt zien dat alleen de tekst tussen de opgegeven tekens wordt geëxtraheerd, zoals weergegeven in de bovenstaande schermafbeelding.

3.1.3 Extraheer tekst tussen twee karakters met een geweldige tool

Hier beveelt het ten zeerste aan Extraheer tekenreeksen tussen opgegeven tekst kenmerk van Kutools for Excel om u te helpen eenvoudig tekst tussen twee dezelfde of verschillende tekens in een cel in Excel te extraheren.

1. Selecteer een lege cel om het resultaat uit te voeren en klik op Kutools > Formule Helper > Formule Helper.

2. In de Formules Helper dialoogvenster, voer dan de volgende instellingen uit.

2.1) Controleer het Filteren en voer vervolgens het woord in "Extract" in het tekstvak;

2.2) In de Kies een formulelijst vak, klik op het Extraheer tekenreeksen tussen opgegeven tekst keuze;

2.3) In de Argumenten ingevoerd sectie:

In het Cel selecteer een cel waar u de tekst wilt extraheren (hier selecteer ik cel B5);

In het Start char (s) voer het startteken van de twee verschillende tekens in;

In het Einde char(s), voert u het eindteken van de twee verschillende tekens in.

2.4) Klik op Ok. Zie screenshot:

3. Dan wordt alleen de tekst tussen "<" en ">" in cel B5 geëxtraheerd. Ondertussen is er een formule gemaakt, u kunt deze resultaatcel selecteren en vervolgens de hendel voor automatisch aanvullen naar beneden slepen om teksten uit andere cellen in dezelfde lijst te extraheren.

Klik om meer over deze functie te weten.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

3.1.4 Extraheer tekst tussen twee tekens (inclusief de tekens) per regel

Als u de twee tekens na het uitpakken wilt behouden, probeer dan een regel toe te passen in de Extraheer tekst functie van Kutools for Excel.

1. klikken Kutools > Tekst > Extraheer tekst.

2. In de Extraheer tekst dialoogvenster, voer dan de volgende instellingen uit.

2.1) Klik op de Extract op regel tab;

2.2) In de Bereik sectie, klik op de

knop om het celbereik te selecteren waar u de tekst tussen tekens wilt extraheren;

2.3) In de Tekst vakje, enter <*>;

Tips: "<"En">” zijn de tekens waartussen u tekst wilt extraheren, * is een jokerteken dat een willekeurig aantal tekens vertegenwoordigt. U kunt de voorwaarde volgens uw behoeften invoeren.

2.4) Klik op de Toevoegen knop om de voorwaarde toe te voegen aan de Regelbeschrijving keuzelijst;

2.5) Klik op de Ok knop. Zie screenshot:

3. Een ander Extraheer tekst dialoogvenster verschijnt, selecteer een cel om het resultaat uit te voeren en klik vervolgens op de OK knop.

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

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

3.2 Tekst extraheren tussen twee woorden

Naast het extraheren van tekst tussen twee tekens, moet u mogelijk ook tekst tussen twee woorden extraheren. Extraheer bijvoorbeeld alle tekstreeksen tussen twee woorden "KTE" en "feature" uit elke cel in kolom B, zoals weergegeven in de onderstaande schermafbeelding. U kunt een van de volgende methoden proberen om het voor elkaar te krijgen.

3.2.1 Tekst extraheren tussen twee woorden met formule

U kunt een formule gebruiken die is gebaseerd op de MID-functie en de SEARCH-functie om alle tekenreeksen tussen twee woorden in een cel te extraheren.

Generieke 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;

Start_woord: het startwoord van de twee woorden waarvan u alle tekstreeksen erna wilt extraheren;

Lengte1: de tekenlengte van het startwoord.

Eindwoord: het eindwoord van de twee woorden waarvan u alle tekstreeksen ervoor wilt extraheren.

Lengte2: de tekenlengte van het startwoord plus 1.

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

Opmerking:: In de formule staat het getal 3 voor de tekenlengte van het woord "KTE"; het cijfer 4 staat voor de tekenlengte van het woord "KTE" plus 1.

U kunt zien dat alle tekstreeksen tussen de opgegeven twee woorden uit elke cel in kolom B worden geëxtraheerd.

3.2.2 Extraheer tekst tussen twee woorden met een geweldige tool

Voor veel Excel-gebruikers zijn formules misschien moeilijk te onthouden en te hanteren. Hier, met de Extraheer tekenreeksen tussen opgegeven tekst kenmerk van Kutools for Excel, kunt u met slechts een paar klikken eenvoudig tekst tussen twee woorden extraheren.

1. Selecteer een cel om het resultaat uit te voeren en klik vervolgens op Kutools > Formule Helper > Formule Helper.

2. In de Formule Helper dialoogvenster, moet u als volgt configureren.

2.1) Controleer het Filteren en voer vervolgens het woord in "Extract" in het tekstvak;

2.2) In de Kies een formulelijst vak, klik op het Extraheer tekenreeksen tussen opgegeven tekst keuze;

2.3) In de Argumenten ingevoerd sectie:

In het Cel selecteer een cel waar u de tekst wilt extraheren (hier selecteer ik cel B5);

In het Start char (s) voer het startwoord in van de twee woorden waarvan u alle tekstreeksen erna wilt extraheren;

In het Eindteken(s) voert u het eindwoord in van de twee woorden waarvan u alle tekstreeksen ervoor wilt extraheren.

2.4) Klik op Ok. Zie screenshot:

3. Vervolgens worden alle tekstreeksen tussen twee woorden "KTE" en "feature" in cel B5 geëxtraheerd. Ondertussen is er een formule gemaakt, u kunt deze resultaatcel selecteren en vervolgens de hendel voor automatisch aanvullen naar beneden slepen om teksten uit andere cellen in dezelfde lijst te extraheren.

Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.

Getallen extraheren op positie

Voor een lijst met alfanumerieke tekenreeksen kunnen er drie gevallen zijn:

Het nummer staat aan het begin van de tekst;
Het nummer staat aan het einde van de tekst;
Het nummer kan overal in de tekst staan.

In deze sectie zullen we verschillende methoden bieden die kunnen worden gebruikt om de getallen te extraheren in elk van de bovengenoemde gevallen.

1 Extraheer nummer links van een tekenreeks

Dit deel introduceert een formule waarmee u alleen getallen kunt extraheren die vóór tekst in een cel verschijnen.

Generieke 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 nummer wilt extraheren aan de linkerkant van de tekenreeks.

Opmerking:

1) Als u Excel 2019 en eerder gebruikt, moet u op de Ctrl + Shift + Enter toetsen om deze matrixformule te bevestigen.

2) Als u Excel 365 of Excel 2021 gebruikt, bevestigt u deze formule met de Enter sleutel.

Selecteer een lege cel, voer de onderstaande formule in en druk op Ctrl + Shift + Enter or Enter toets om het resultaat te krijgen. Selecteer deze resultaatcel en sleep vervolgens de hendel voor automatisch aanvullen naar beneden om het aantal andere cellen te krijgen.

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

Opmerkingen:

1) Als een cel alleen getallen bevat, wordt het hele getal geëxtraheerd.

2) Deze formule extraheert alleen de getallen aan de linkerkant van een tekenreeks. Als er getallen in het midden of aan het einde van de tekstreeks staan, worden ze genegeerd.

2 Haal het nummer uit de rechterkant van een tekenreeks

Zoals te zien is in de onderstaande schermafbeelding, probeert u de volgende formule om alleen de getallen te extraheren die na tekst in een cel verschijnen.

Generieke 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 een getal wilt extraheren vanaf het begin van de tekenreeks.

Opmerking:

1) Als u Excel 2019 en eerder gebruikt, moet u op de Ctrl + Shift + Enter toetsen om deze matrixformule te bevestigen.

2) Als u Excel 365 of Excel 2021 gebruikt, bevestigt u deze formule met de Enter-toets.

=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 getallen bevat, wordt het hele getal geëxtraheerd.

2) Deze formule haalt alleen de getallen uit de rechterkant van een tekstreeks. Als er getallen in het midden of aan het begin van de tekenreeks staan, worden ze genegeerd.

3. Extraheer alle getallen vanaf elke positie in een tekenreeks

De bovenstaande methoden helpen om het nummer alleen links of rechts van een tekstreeks te extraheren. Als u alle getallen overal in een tekstreeks wilt extraheren, bieden we hier drie methoden om dit voor elkaar te krijgen.

3.1 Extraheer alle getallen van overal in een string met formule

U kunt de volgende formule toepassen om alle getallen overal in een tekstreeks in Excel te extraheren.

1. Selecteer een lege cel, kopieer of voer de onderstaande formule in en druk op de Enter toets 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 vervolgens de hendel voor automatisch aanvullen naar beneden om alle aantallen andere cellen te krijgen.

3.2 Extraheer alle getallen van overal in een string met VBA

Bovenstaande formule is voor veel Excel-gebruikers te lang en te ingewikkeld. U kunt eigenlijk een VBA-script uitvoeren om de taak in Excel te automatiseren. U kunt als volgt doen.

1. druk de anders + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. In de opening Microsoft Visual Basic voor toepassingen venster klikt Invoegen > Module. Kopieer vervolgens de onderstaande VBA naar het modulecodevenster.

VBA-code: extraheer alle getallen overal in een tekstreeks

Sub ExtrNumbersFromRange() 'Bijgewerkt door 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 ="Applicatie selecteren.InputBoxs( :", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Sub Set afsluiten xRRg = Application.InputBox("Selecteer output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Dan 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)) Dan strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Volgende xRg End Sub

3. druk de F5 sleutel om de code uit te voeren. In de opening KutoolsforExcel dialoogvenster, selecteer het celbereik waar u alle getallen uit elke cel wilt extraheren en klik vervolgens op de OK knop.

4. Dan nog een KutoolsforExcel dialoogvenster verschijnt. Selecteer in dit dialoogvenster een doelcel en klik op OK.

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

4. Extraheer nummers na specifieke tekst

Zoals te zien is in de onderstaande schermafbeelding, biedt deze sectie twee methoden om u te helpen dit voor elkaar te krijgen om getallen na de specifieke tekst "Nee" te extraheren.

4.1 Extraheer getallen na een specifieke tekst met formule

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

Generieke 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 wilt extraheren na een specifieke tekst;

Tekst: De tekst waarvan 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, retourneert de formule #N/A.

2) De formule is hoofdlettergevoelig.

4.2 Getallen extraheren na een specifieke tekst met door de gebruiker gedefinieerde functie

De volgende door de gebruiker gedefinieerde functie kan ook helpen om getallen te extraheren na een specifieke tekst in een cel. Ga als volgt te werk.

1. druk de anders + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. In de Microsoft Visual Basic voor toepassingen venster klikt Invoegen > module, en kopieer vervolgens de onderstaande VBA-code naar het modulecodevenster.

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

Functie GetNumberAfterTheChar (Rng As Range, Char As String) 'Bijgewerkt door 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 = "" Functie beëindigen als xStart < 1 Dan GetNumberAfterTheChar = "" Functie beëindigen End If xStart = xStart - 1 + Len(Char) Als xStart < 1 Dan GetNumberAfterTheChar = "" Functie beëindigen 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 Selecteer Next GetNumberAfterTheChar = xRntString End Function

3. druk de anders + Q toetsen om de Microsoft Visual Basic voor toepassingen venster.

4. Selecteer een cel, voer de onderstaande formule in en druk op de Enter sleutel. Selecteer deze resultaatcel en sleep de hendel voor automatisch aanvullen naar beneden om deze formule op andere cellen toe te passen.

=GetNumberAfterTheChar(B5,"No. ")

Opmerkingen:

1) In deze formule is B5 de cel waar u het nummer wilt extraheren, en "Nee. ” is de specifieke tekst waarvan u het nummer erna wilt extraheren. U kunt ze naar behoefte wijzigen.

2) Als er een scheidingsteken staat tussen de specifieke tekst en het nummer, voeg dan het scheidingsteken toe aan het einde van de tekst. In dit geval heb ik een spatie toegevoegd na de tekst "Nee.", die uiteindelijk wordt weergegeven als "Nee. ”.

3) Deze methode is niet hoofdlettergevoelig;

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

Gerelateerde artikelen:

Excel-zelfstudie: tekst-, getal- en datumcellen splitsen (gescheiden in meerdere kolommen)
Deze tutorial is verdeeld in drie delen: gesplitste tekstcellen, gesplitste nummercellen en gesplitste datumcellen. Elk deel biedt verschillende voorbeelden om u te helpen weten hoe u de kloofklus moet aanpakken wanneer u hetzelfde probleem tegenkomt.
Klik om meer te weten ...

Excel Tekst en nummer toevoegen aan opgegeven celpositie
In Excel is het toevoegen van teksten of getallen aan cellen een veel voorkomende taak. Zoals het toevoegen van spatie tussen namen, het toevoegen van een voor- of achtervoegsel aan cellen, het toevoegen van streepjes aan sociale nummers. Hier in deze zelfstudie bevat het bijna alle scenario's voor het toevoegen in Excel en biedt het de bijbehorende methoden voor u.
Klik om meer te weten ...

Excel Tekens, woorden en cijfers uit tekstreeksen verwijderen
Stel dat u een lange lijst met tekenreeksen hebt die tekens, cijfers of andere specifieke symbolen bevatten. In bepaalde gevallen moet u mogelijk enkele tekens verwijderen op basis van positie, zoals van rechts, links of in het midden van de tekstreeksen, of enkele ongewenste tekens of cijfers uit de lijst met reeksen verwijderen. Als u de oplossingen één voor één vindt, krijgt u hoofdpijn, deze tutorial verzamelt allerlei methoden voor het verwijderen van tekens, woorden of cijfers in Excel.
Klik om meer te weten ...

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.

Lees verder... Gratis download... Aankoop...

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!