Een getal uit een tekst in Excel halen: de juiste aanpak kiezen

Wanneer je in Excel werkt aan het opschonen van data, kun je de taak tegenkomen om getallen uit alfanumerieke tekenreeksen te halen. Dit probleem is gebruikelijk als je data hebt waarbij getallen in tekst zijn ingebed, zoals productcodes of enquêteresultaten.

In dit artikel laat ik je verschillende methoden zien om getallen uit tekst te halen met formules als RIGHT(), LEN() en FIND() voor verschillende use-cases. Als je niet bekend bent met sommige basisprincipes, raad ik je aan onze cursus Introduction to Excel te volgen om een solide basis in Excel op te bouwen. Bekijk ook onze Data Preparation in Excel om meer te leren over het transformeren van ruwe data.

Excel-formules om getallen uit een tekst te halen

Excel biedt verschillende op formules gebaseerde methoden om getallen uit tekst te halen. Elke methode is geschikt voor andere behoeften, afhankelijk van waar het getal zich in de tekst bevindt. Heb je weinig tijd, voel je dan vrij om direct door te gaan naar de sectie die je meest directe behoefte oplost.

Getallen uit het begin van een tekst in Excel halen

Soms kom je situaties tegen waarin getallen aan het begin van de tekst staan. In zulke gevallen gebruik je LEFT() en FIND() om de getallen uit de tekst te halen. Wanneer je de functies in een formule combineert, worden de getallen met de volgende logica opgehaald:

• FIND(): Zoekt de positie van de eerste letter in de tekst.

• LEFT(): Haalt de tekens op vanaf het begin van de tekst tot aan de positie van de eerste letter min één.

Het eerste deel in de onderstaande formule zoekt de eerste letter in de tekst. Daarna haalt het tweede deel alles vóór die letter op, waardoor alleen de voorste getallen overblijven. Als er geen letters zijn, wordt de hele tekst geretourneerd.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Getallen uit het begin van een tekst in Excel halen. Afbeelding door de auteur.

Deze methode is geschikt bij datasets die vaak numerieke voorvoegsels gebruiken, zoals ordernummers, lid-ID’s en batchnummers.

Getallen uit het einde van een tekst in Excel halen

Als je getallen uit het einde van een tekst in Excel wilt halen, gebruik je de functies RIGHT(), LEN() en FIND(). Stel dat je de volgende data in het Excel-werkblad hebt. Je combineert de functies om het getal op te halen waarbij:

• FIND(): De positie van het eerste cijfer in de tekst vindt.

• LEN(): Het aantal tekens berekent vanaf het eerste cijfer tot het einde van de tekst.

• RIGHT(): De tekens ophaalt vanaf het eerste cijfer tot het einde van de tekst.

De onderstaande formule verwijdert alle voorlooptekst en retourneert het eerste getal samen met alles wat daarop volgt in de tekst.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Getallen uit het einde van een tekst in Excel halen. Afbeelding door de auteur.

Deze methode is het handigst bij datasets met consistente alfanumerieke tekens, zoals product-SKU’s, order-ID’s en serienummers.

Getallen uit eender welke positie in een tekst in Excel halen

Als je een dataset hebt waarin de getallen ergens in de tekst kunnen voorkomen, heb je geavanceerde formules nodig met TEXTJOIN(), MID() en ROW() om de getallen op te halen.

Door deze functies te combineren in een formule haal je een getal uit een tekst met de volgende logica: 

• ROW() en MID(): Doorloopt elk teken in de tekst om te bepalen of het numeriek is.

• IFERROR(): Logische test om de geïdentificeerde getallen te isoleren van andere tekens.

• TEXTJOIN(): Combineert alle opgehaalde numerieke tekens tot één nummertekenreeks.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Getal uit eender welke positie in een tekst halen. Afbeelding door de auteur.

Deze methode is het best geschikt wanneer je dataset alfanumerieke tekens bevat met getallen die overal in de tekst kunnen voorkomen. Let op: deze functies zijn niet allemaal beschikbaar in oudere versies van Excel. Bekijk onze skill track Excel Fundamentals om meer te leren over de functies en formules in de nieuwste Excel-versies.

Getallen ophalen door ongewenste tekens te filteren

Soms is je data rommelig. Gelukkig kun je de functies SUBSTITUTE() en TEXTJOIN() combineren. Volg gewoon deze stappen:

• SUBSTITUTE(): Met de functie SUBSTITUTE() kun je alle niet-numerieke tekens in een tekst vervangen door een scheidingsteken, zoals een spatie of een lege tekenreeks. Je kunt bijvoorbeeld letters en speciale tekens door spaties vervangen.

• TEXTJOIN(): Met de functie TEXTJOIN() kun je vervolgens de overgebleven tekens (die nu alleen cijfers zouden moeten zijn) samenvoegen tot één tekenreeks.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Getal uit een tekst halen door tekens te filteren. Afbeelding door de auteur.

Wanneer je mogelijk getallen uit een tekst in Excel moet halen

Laten we nu twee kleine casestudy’s bekijken om te oefenen met het halen van getallen uit tekst.

Voorbeeld 1: Getallen uit productcodes halen

Stel dat je een productvoorraad beheert waarbij elk item wordt geïdentificeerd door een unieke productcode. Deze productcodes zijn alfanumerieke tekenreeksen zoals "PROD1234", "ITEM5678" of "SKU91011".

Om het numerieke product-ID uit alfanumerieke productcodes te halen, gebruiken we functies op basis van de positie van het getal. Onthoud: als de getallen aan het einde staan, pas je RIGHT(), LEN() en FIND() toe. Als ze aan het begin staan, gebruik je LEFT() en FIND().

In het onderstaande voorbeeld wordt het numerieke deel van de productcodes vanaf de rechterkant van de code opgehaald.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Getallen uit productcodes in Excel halen. Afbeelding door de auteur.

Voorbeeld 2: Enquêtedata met ingebedde getallen

Enquêtedata bevat soms zowel tekst als getallen zoals "Rated 5 out of 10", "Score: 8" of "3 stars". We moeten dan wat werk verrichten voordat we gemiddelden kunnen berekenen of trends kunnen identificeren.

Omdat getallen overal in de tekst kunnen voorkomen, is een dynamische formule met TEXTJOIN(), MID() en ROW() nodig.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Getallen uit eender welke positie in Excel halen. Afbeelding door de auteur.

Kopieer-en-plak codevoorbeelden voor speciale gevallen

In de laatste sectie geef ik je enkele extra formules die verschillende en specifieke scenario’s afdekken. Kopieer en plak gerust, hopelijk lost het een specifiek probleem voor je op. 

Het eerste aaneengesloten blok met cijfers ophalen

Als je tekst meerdere numerieke reeksen bevat maar je alleen de eerste groep nodig hebt, probeer dan deze oplossing met de functies LET() en SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Het eerste aaneengesloten blok uit een tekst in Excel halen. Afbeelding door de auteur.

Getallen binnen haakjes ophalen

Voor gevallen waarin getallen tussen haakjes staan, gebruik je:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Getallen binnen haakjes in Excel ophalen. Afbeelding door de auteur.

Getallen na een specifiek teken ophalen

Wanneer je data een dubbelepunt als scheidingsteken gebruikt (bijvoorbeeld "Score: 8"), haal je de getallen op met:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Getal na een speciaal teken in Excel halen. Afbeelding door de auteur.

Dit haalt alles na de dubbelepunt op. Omwikkel het resultaat met VALUE() als je een numerieke waarde nodig hebt. Vervang de dubbelepunt als je een ander scheidingsteken gebruikt.

Alle getallen ophalen en samenvoegen tot één waarde

Om alle niet-cijfertekens te verwijderen en elk numeriek teken in een tekst samen te voegen, gebruik je dit:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Alle getallen ophalen en samenvoegen tot één waarde in Excel. Afbeelding door de auteur.

Tip: voer dit in oudere versies van Excel in als een matrixformule (Ctrl+Shift+Enter).

Getallen inclusief decimalen ophalen

Voor numerieke tekenreeksen die een decimaal kunnen bevatten (bijv. "Price: 45.99"), gebruik je deze formule om zowel de cijfers als de decimale punt te behouden:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Getallen inclusief decimalen in Excel ophalen. Afbeelding door de auteur.

Opmerking: deze formule gaat uit van slechts één decimale punt.

Conclusie

Getallen uit tekst halen is een belangrijke techniek voor het opschonen van data. Als data-analist moedig ik je aan om te leren hoe je de verschillende formules in dit artikel toepast.

Wil je je Excel-vaardigheden verder ontwikkelen, dan raad ik je aan onze cursussen Data Analysis in Excel en Data Visualization in Excel te volgen om data-analyse en -presentatie onder de knie te krijgen. Probeer ook onze  Financial Modeling in Excel-cursus, die veel deuren kan openen.