Excel-substringtechnieken: tekst extraheren en opmaken

Tekst extraheren op positie met Excel-substringfuncties

Vaak willen we specifieke delen van een tekststring, zoals een woord of reeks tekens, extraheren op basis van hun positie. Excel biedt verschillende ingebouwde functies om je te helpen specifieke delen van tekststrings te extraheren. Enkele van de meest gebruikte functies zijn LEFT(), RIGHT(), MID(), TEXTBEFORE() en TEXTAFTER(). Laten we ze stuk voor stuk bekijken. 

De LEFT()-Excel-substringfunctie gebruiken

Je kunt de functie LEFT() gebruiken om tekens uit het begin van een tekststring te extraheren. De syntaxis is:

=LEFT(text, [num_chars])

Hier: 

• text verwijst naar het celadres waar de oorspronkelijke tekststring staat.

• num_chars verwijst naar het aantal tekens dat wordt geëxtraheerd. De standaardwaarde is 1.

Laten we een voorbeeld bekijken. Hier heb ik enkele productcodes en wil ik de eerste drie tekens daaruit halen. 

=LEFT(A2,3)

Haal de eerste drie tekens op met de functie LEFT(). Afbeelding door auteur.

De RIGHT()-Excel-substringfunctie gebruiken

De functie RIGHT() in Excel extraheert tekens uit het einde van een tekststring. De syntaxis is:

=Right(text, [num_chars])

Hier: 

• text verwijst naar het celadres waar de oorspronkelijke tekststring staat.

• num_chars verwijst naar het aantal tekens dat wordt geëxtraheerd. De standaardwaarde is 1.

Laten we een voorbeeld bekijken. Hier gebruik ik de functie RIGHT() om de laatste drie tekens te extraheren.

=RIGHT(A2, 3)

Haal de laatste drie tekens op met de functie RIGHT(). Afbeelding door auteur.

De MID()-Excel-substringfunctie gebruiken

De functie MID() extraheert tekens uit het midden van een tekststring. De syntaxis is:

=MID(text, start_num, num_chars)

Hier:

• text verwijst naar het celadres waar de oorspronkelijke tekststring staat.

• num_chars verwijst naar het aantal tekens dat wordt geëxtraheerd. De standaardwaarde is 1.

• start_num verwijst naar de startpositie binnen de tekststring.

In dit voorbeeld gebruik ik de formule MID() om de middelste waarde uit de string te halen vanaf het vierde teken.

=MID(A2,4,3)

Haal de middelste drie tekens op met de functie MID(). Afbeelding door auteur. 

En dat is alles. =MID(A2,4,3) extraheert drie tekens uit de tekst in cel A2, beginnend bij het 4e teken.

De FIND()-Excel-substringfunctie gebruiken

De functie FIND() in Excel lokaliseert een substring binnen een tekststring en retourneert de positie van het eerste teken van de substring binnen de string. De syntaxis is:

FIND(find_text, within_text, [start_num]) 

Hier:

• find_text verwijst naar de tekst waar we naar willen zoeken.

• within_text is de tekst die de te vinden tekst bevat.

• start_num specificeert de positie om de zoekopdracht te beginnen, met standaard 1.

Laten we een eenvoudige dataset bekijken om te laten zien hoe de functie FIND() van Excel werkt. In dit voorbeeld staat de naam DataCamp in de kolom Text, en ik wil de positie van de substring Camp vinden. Dit retourneert 5 omdat Camp begint bij het vijfde teken in de tekst.

=FIND("Camp", A2)

De functie FIND() gebruiken om de positie van de tekst te bepalen. Afbeelding door auteur.

De SUBSTITUTE()-Excel-substringfunctie gebruiken

De functie SUBSTITUTE() vervangt een specifieke substring door een andere. De syntaxis is:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Hier:

• text bevat de tekst die je wilt wijzigen.

• old_text verwijst naar de tekst die je wilt vervangen.

• new_text is de tekst waarmee je de oude tekst wilt vervangen.

• instance_num specificeert welke voorkomens van de tekst je wilt wijzigen. Als dit niet is opgegeven, wordt elke instantie vervangen. 

Om dit te begrijpen, bekijken we een eenvoudige dataset met tekststrings met specifieke scheidingstekens. De kolom Text bevat namen en leeftijden, geformatteerd met puntkomma’s als scheidingsteken. Om dit op te schonen, typ ik de volgende formule:

=SUBSTITUTE(A2, ";" , ",")

Hier:

• A2 verwijst naar de cel met de oorspronkelijke tekst.

• ";" specificeert de oude tekst die we gaan wijzigen.

• "," is de tekst waarmee we de oude tekst willen vervangen.

De functie SUBSTITUTE gebruiken om oude tekst te vervangen. Afbeelding door auteur.

En dat is het. Door onze formule toe te passen in cel B2 vervangt Excel de puntkomma door een komma.

Tekst extraheren met scheidingstekens met Excel-substringfuncties

Scheidingstekens zijn de specifieke tekens die twee tekststrings scheiden, zoals een puntkomma of een komma. In Excel kun je de functies TEXTBEFORE() en TEXTAFTER() gebruiken om tekst te extraheren wanneer er scheidingstekens aanwezig zijn.  

De TEXTBEFORE()-Excel-substringfunctie gebruiken 

Zoals de naam al aangeeft, extraheert de functie TEXTBEFORE() tekst die vóór een opgegeven teken of substring staat. De syntaxis is als volgt:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Hier: 

• text verwijst naar de oorspronkelijke tekst.

• delimiter is meestal een komma of een puntkomma.

• instance_num is de gewenste voorkomst van het scheidingsteken. De standaardwaarde is 1.

• match_mode geeft aan of de zoekopdracht naar het scheidingsteken hoofdlettergevoelig moet zijn (TRUE, standaard) of niet (FALSE).

• match_end bepaalt of het einde van de tekststring als scheidingsteken moet worden behandeld. Als TRUE, retourneert de functie de oorspronkelijke tekst als het scheidingsteken niet wordt gevonden.

• if_not_found specificeert een aangepaste waarde om te retourneren als het scheidingsteken niet wordt gevonden.

Laten we nu een voorbeeld nemen om deze formule in actie te zien. Ik heb een kolom Text met personeelsgegevens, waaronder hun namen, afdelingen en vestigingslocaties. Ik wil nu alleen de namen en afdelingen extraheren.

=TEXTBEFORE(A2, ",", 2, 1, 1)

De functie TEXTBEFORE() gebruiken om data te extraheren. Afbeelding door auteur 

Zo werkt dit: 

• A2 bevat de tekst waaruit ik wil extraheren. 

• "," is het scheidingsteken dat de tekst in mijn oorspronkelijke data splitst. 

• 2 betekent dat de functie de tweede voorkomst van het scheidingsteken neemt.

• 1 negeert eventuele hoofdlettergevoelige gevallen.

• 1 (aan het eind) retourneert de oorspronkelijke tekst als het scheidingsteken ontbreekt.

De TEXTAFTER()-Excel-substringfunctie gebruiken

De functie TEXTAFTER() lijkt op de functie TEXTBEFORE()—het enige verschil zit in het resultaat. De functie TEXTAFTER() extraheert tekst die na een scheidingsteken komt. De syntaxis is als volgt:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Hier: 

• text verwijst naar de oorspronkelijke tekst.

• delimiter is een komma of een puntkomma. 

• instance_num is de gewenste voorkomst van het scheidingsteken. De standaardwaarde is 1.

• match_mode geeft aan of de zoekopdracht naar het scheidingsteken hoofdlettergevoelig moet zijn (TRUE, standaard) of niet (FALSE).

• match_end bepaalt of het einde van de tekststring als scheidingsteken moet worden behandeld. Als TRUE, retourneert de functie de oorspronkelijke tekst als het scheidingsteken niet wordt gevonden.

• if_not_found specificeert een aangepaste waarde om te retourneren als het scheidingsteken niet wordt gevonden.

Hier heb ik een kolom Text met personeelsgegevens, waaronder hun namen, afdelingen en vestigingslocaties. Hieruit wil ik de locaties en ID’s van de werknemers extraheren. 

=TEXTAFTER(A2,",",2,1,1)

De functie TEXTAFTER() gebruiken om data te extraheren. Afbeelding door auteur 

Zo werkt dit: 

• A2 bevat de tekst waaruit ik wil extraheren. 

• ","  is het scheidingsteken dat de tekst in mijn oorspronkelijke data splitst. 

• 2 betekent dat de functie de tweede voorkomst van het scheidingsteken neemt. 

• 1 negeert eventuele hoofdlettergevoelige gevallen.

• 1 (aan het eind) retourneert de oorspronkelijke tekst als het scheidingsteken ontbreekt.

Specifieke Excel-substringtoepassingen

Nu je de basis van substrings kent, is het tijd voor de geavanceerde methoden. Deze methoden maken complexere tekstbewerkingen mogelijk, waardoor je dataverwerking nog efficiënter wordt. 

Omgaan met variabele lengtes van voornamen

Ik heb een dataset met de volledige naam, maar ik wil alleen de voornaam extraheren.

Een tabel met volledige namen. Afbeelding door auteur.

Je denkt vast dat het simpel is—gebruik de functie LEFT() om de voornaam te krijgen. Dus probeerde ik het met de formule:

=LEFT(A2,4)

De functie LEFT() gebruiken om de voornaam op te halen. Afbeelding door auteur.

Maar in plaats van de voornaam kreeg ik de eerste vier tekens van de naam, omdat ik num_char op 4 had gezet, aangezien Jane vier tekens heeft in de voornaam. Als ik de formule kopieer en plak, geeft dat niet het gewenste resultaat voor andere namen.

Ik heb een flexibelere oplossing nodig. Om de voornaam te extraheren, ongeacht de lengte, kun je de functie LEFT() combineren met de functie FIND(). Ik gebruik FIND() om de positie van de eerste spatie in de volledige naam te vinden, die de voor- en achternaam scheidt. Vervolgens gebruik ik LEFT() om tekens tot die positie te extraheren. 

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

Combineer de functies FIND() en LEFT() om de voornaam te extraheren. Afbeelding door auteur.

Ik licht de formule even toe om te laten zien hoe deze werkt:

• Cel A2 bevat de volledige naam.

• FIND(" ", A2, 1) vindt de positie van de eerste spatie in de tekststring in cel A2 vanaf het eerste teken.

• -1 wordt gebruikt om de spatie te verwijderen.

• LEFT(A2, ...) extraheert het opgegeven aantal tekens vanaf links van de tekststring.

Domein uit e-mailadres extraheren

Laten we een ander voorbeeld bekijken om te zien of LEFT() en FIND() een ander scenario aankunnen. Ik heb een andere dataset met e-mailadressen waaruit ik de domeinnaam moet extraheren.

Data met e-mailadressen. Afbeelding door auteur.

Hiervoor maak ik een andere kolom, Email Domain, en kies ik een andere cel om de volgende formule te typen, die ik vervolgens naar drie andere cellen kopieer:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Extraheer de domeinnaam met RIGHT(), LEN() en FIND(). Afbeelding door auteur.

Kijk, daar zijn de domeinnamen—maar laten we begrijpen hoe Excel het gewenste resultaat ophaalt:

• FIND("@", A2) lokaliseert de positie van het @-symbool in het e-mailadres.

• LEN(A2) berekent de totale lengte van het e-mailadres.

• LEN(A2) - FIND("@", A2) berekent het aantal tekens na het @-symbool.

• Uiteindelijk extraheert RIGHT(A2, LEN(A2) - FIND("@", A2)) dat aantal tekens vanaf het rechtereinde van de string.

Productcodes extraheren door LEFT(), MID() en RIGHT() te combineren

Tot nu toe heb ik alleen uitgelegd hoe de functies LEFT(), MID() en RIGHT() afzonderlijk werken. Maar je kunt veel meer doen door deze functies te combineren. Ze kunnen verschillende delen van een tekststring extraheren op basis van specifieke patronen.

Hier heb ik een lijst met productcodes en ik wil daaruit een specifiek deel extraheren. 

• Uit het eerste deel wil ik één teken.
• Uit het tweede deel wil ik alle vier de tekens.
• Uit het laatste deel wil ik de laatste drie tekens extraheren.

Een lijst met productcodes. Afbeelding door auteur.

Om dit te doen, combineer ik de functies LEFT(), RIGHT() en MID() en voer ik de volgende formule in:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Combineer de functies MID(), LEFT() en RIGHT() om data op te halen. Afbeelding door auteur.

Zoals je ziet, heeft deze gecombineerde formule de gewenste tekens uit elk deel van de codes gehaald. Zo:

• LEFT(A2, 1) extraheert het eerste teken.

• MID(A2, FIND("-", A2) + 1, 4) vindt het eerste koppelteken, gaat één teken naar rechts en extraheert vervolgens vier tekens.

• "-" voegt een koppelteken toe.

• RIGHT(A2, 3) extraheert de laatste drie tekens.

• & voegt de tekens samen. 

SUBSTITUTE() met MID() gebruiken voor dynamische tekstreekstractie 

Laten we nu bekijken hoe je de functie MID() kunt combineren met de functie SUBSTITUTE() om specifieke delen van een tekststring te extraheren en vervolgens tekens of substrings binnen dat geëxtraheerde deel te vervangen.

Ik heb bijvoorbeeld Order Details van klanten en wil het order-ID vervangen door de tekst Order confirmed.

Een tabel met orderdetails. Afbeelding door auteur.

Ik gebruik de volgende formule:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

De functies SUBSTITUTE() en MID() om de tekst te vervangen. Afbeelding door auteur.

Dat is het! Je ziet dat deze formule specifieke delen van tekststrings extraheert, zoals ik wilde.

• MID(A2, 1, 19) extraheert de eerste 19 tekens uit de tekst in cel A2.

• SUBSTITUTE(text, old_text, new_text) vervangt voorkomens van old_text door new_text.

Nog een voorbeeld: hier heb ik een kolom Product Detail en ik wil de productmaat uit de vierkante haakjes extraheren.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

De productmaat uit de vierkante haakjes extraheren. Afbeelding door auteur.

En klaar—alles is geëxtraheerd.

• FIND("[", A2) vindt de beginpositie van het vierkante haakje.

• FIND("]", A2) vindt de eindpositie van het vierkante haakje.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) extraheert de tekst tussen de twee haakjes.

• SUBSTITUTE(...,"[", "") verwijdert het openingshaakje uit de geëxtraheerde tekst.

Data-invoer opschonen

Soms is onze data rommelig, maar dat betekent niet dat je die data zo in je processen moet gebruiken. Hier heb ik een lijst met phone numbers in meerdere formaten, maar ik wil ze standaardiseren in een consistent formaat door inconsistenties in de invoer op te schonen.

Ongeformatteerde telefoonnummers. Afbeelding door auteur.

Om dit te standaardiseren, maak ik een andere kolom met de naam Clean data. Ik typ de volgende formule in de tweede cel en kopieer deze formule naar alle cellen waar ik de resultaten wil zien:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Hier: 

• SUBSTITUTE(A2, "-", "") verwijdert koppeltekens.

• SUBSTITUTE(..., "(", "") verwijdert haakjes openen.

• SUBSTITUTE(..., ")", "") verwijdert haakjes sluiten.

• SUBSTITUTE(..., ".", "") verwijdert punten.

Dit zet de verschillende formaten om in een aaneengesloten reeks cijfers, zoals hieronder te zien. De data is nu opgeschoond, netjes opgemaakt en klaar voor gebruik. Je kunt het zo laten als je wilt.

Ruim de data op met de functie SUBSTITUTE(). Afbeelding door auteur.

Tekst opmaken voor rapporten

Als je na het opschonen nog een stap verder wilt gaan en de tekst passend wilt opmaken, kun je ook verschillende substringfuncties gebruiken.

Neem het vorige voorbeeld, waarin ik de telefoonnummers heb opgeschoond. Nu wil ik de opmaak van de nummers veranderen zodat ze meer op telefoonnummers lijken en niet alleen op een reeks cijfers. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Gestandaardiseerd formaat van nummers met meerdere substrings. Afbeelding door auteur.

En klaar. Ik heb nu alle nummers zoals ik ze wilde. Zo werkte deze formule:

• LEFT(B2, 3) extraheert de eerste drie cijfers.

• MID(B2, 4, 3) extraheert de volgende drie cijfers vanaf de vierde positie.

• RIGHT(B2, 4) extraheert de laatste vier cijfers.

• TEXT(..., "000") and TEXT(..., "0000") formatteert elk deel om het juiste aantal cijfers te garanderen met eventuele voorloopnullen.

• & "-" & voegt de opgemaakte delen samen met koppeltekens.

Tot slot

Je hebt nu geleerd hoe je de substringfuncties van Excel zoals LEFT(), RIGHT(), MID(), TEXTBEFORE() en TEXTAFTER() gebruikt om tekstdata te bewerken. Of je nu specifieke delen uit een string haalt of rommelige data opschoont, deze functies kunnen je werk een stuk eenvoudiger en efficiënter maken.

Maar er is altijd meer te leren met Excel. De cursus Introduction to Excel is een perfecte volgende stap als je net begint. Als je meer wilt leren over data-analyse, probeer dan de cursus Data Analysis in Excel, die goed aansluit bij onze Data Manipulation in Excel Cheat Sheet. Deze helpen je een sterke basis op te bouwen en je vaardigheden naar een hoger niveau te tillen.