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.
Inhoudsopgave: [ Verbergen ]
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
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
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.
Note: 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
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
=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.
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
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
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.
=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
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
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
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 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)
Or
=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.
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
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
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.
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 andere resultaten te krijgen.
=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
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 deze formule op andere cellen toe te passen.
=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
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
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:
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
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:
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.
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:
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
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.
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.
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
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(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
Note: 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.
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
Opmerking:
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:
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
Opmerking:
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.
=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 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()
'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 de F5 sleutel om de code uit te voeren. In de opening KutoolsvoorExcel dialoogvenster, selecteer het celbereik waar u alle getallen uit elke cel wilt extraheren en klik vervolgens op de OK knop.
4. Dan nog een KutoolsvoorExcel 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
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.
=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 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
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 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:
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 ...
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
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 honderden muisklikken voor u elke dag!