Download het voorbeeldwerkboek
Deze tutorial zal laat u zien hoe u een getal uit een tekstreeks kunt vinden en extraheren in Excel en Google Spreadsheets.
Zoek en extraheer nummer uit string
Soms kunnen uw gegevens cijfers en tekst bevatten en wilt u de numerieke gegevens extraheren.
Als het nummergedeelte zich aan de rechter- of linkerkant van de string bevindt, is het relatief eenvoudig om het nummer en de tekst te splitsen. Als de cijfers echter binnen de tekenreeks staan, d.w.z. tussen twee tekstreeksen, moet u een veel gecompliceerdere formule gebruiken (hieronder weergegeven).
TEXTJOIN - Nummers extraheren in Excel 2016+
In Excel 2016 is de TEXTJOIN-functie geïntroduceerd, die de getallen overal in de tekstreeks kan extraheren. Hier is de formule:
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3,RIJ(INDIRECT("1:"&LEN(B3))),1)*1),"")) |
Laten we eens kijken hoe deze formule werkt.
De ROW-, INDIRECT- en LEN-functies retourneren een reeks getallen die overeenkomen met elk teken in uw alfanumerieke reeks. In ons geval heeft "Monday01Day" 11 tekens, dus de ROW-functie retourneert dan de array {1;2;3;4;5;6;7;8;9;10;11}.
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3, {1;2;3;4;5;6;7;8;9;10;11},1)*1),"")) |
Vervolgens extraheert de MID-functie elk teken uit de tekenreeks, waardoor een array wordt gemaakt met uw originele tekenreeks:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),"")) |
Door elke waarde in de array met 1 te vermenigvuldigen, ontstaat een array met fouten. Als het arrayteken tekst was:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;0;1;#WAARDE!;#WAARDE!;#WAARDE! !}),"")) |
Vervolgens verwijdert de IFERROR-functie de foutwaarden:
| 1 | =TEXTJOIN("",TRUE,{"";"";"";"";"";"";0;1;"";"";""}) |
Laat alleen de TEXTJOIN-functie over die de resterende nummers samenvoegt.
Merk op dat de formule je alle numerieke tekens samen uit je string geeft. Als uw alfanumerieke tekenreeks bijvoorbeeld Monday01Day01 is, krijgt u 0101 als resultaat.
Nummers extraheren - vóór Excel 2016
Vóór Excel 2016 kon u een veel gecompliceerdere methode gebruiken om getallen uit tekst te extraheren. U kunt de FIND-functie samen met de IFERROR- en MIN-functies gebruiken om zowel de eerste positie van het nummergedeelte als de eerste positie van het tekstgedeelte na het nummer te bepalen. Dan extraheren we eenvoudig het nummergedeelte met de MID-functie.
| 1 | =MID(B3,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999)),MIN(IFERROR(SEARCH({"a") ,"b", "c", "d", "e", "f", "g", "h", "ik", "j", "k", "l", "m", n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" },B3,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(IFERROR(FIND( {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Opmerking: dit is een matrixformule, u moet de formule invoeren door op CTRL + SHIFT + ENTER te drukken in plaats van alleen op ENTER.
Laten we stap voor stap bekijken hoe deze formule werkt.
Vind vuistnummer
We kunnen de FIND-functie gebruiken om de startpositie van het nummer te lokaliseren.
| 1 | = MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B3),999999999)) |
Voor het argument find_text van de functie FIND gebruiken we de matrixconstante {0,1,2,3,4,5,6,7,8,9}, waardoor de functie FIND een afzonderlijke zoekopdracht uitvoert voor elke waarde in de matrixconstante.
Het argument inside_text van de FIND-functie is in ons geval Monday01Day, waarin 1 te vinden is op positie 8 en 0 op positie 7, dus onze resultaatarray zal zijn: {8,#VALUE,#VALUE,#VALUE, #WAARDE,#WAARDE,#WAARDE, #WAARDE, #WAARDE,7}.
Met behulp van de IFERROR-functie vervangen we de #VALUE-fouten door 999999999. Dan zoeken we eenvoudig naar het minimum binnen deze array en krijgen daarom de plaats van het eerste getal (7).
Houd er rekening mee dat de bovenstaande formule een matrixformule is, u moet op Ctrl+Shift+Enter drukken om deze te activeren.
Zoek eerste tekstteken na nummer
Net als bij het lokaliseren van het eerste getal in de tekenreeks, gebruiken we de FIND-functie om te bepalen waar de tekst opnieuw begint na het getal, waarbij ook goed gebruik wordt gemaakt van het start_num-argument van de functie.
| 1 | =MIN(IFERROR(FIND({"a","b","c","d","e","f","g","h","ik","j","k ","l","m","n","o","p","q","r","s","t","u","v","w", "x", "y", "z"},B3,C3),999999999)) |
Deze formule werkt op dezelfde manier als de vorige die werd gebruikt voor het lokaliseren van het eerste getal, alleen gebruiken we letters in onze matrixconstante en daarom veroorzaken getallen #VALUE-fouten. Houd er rekening mee dat we pas beginnen met zoeken na de positie die is bepaald voor het eerste getal (dit is het start_num-argument) en niet vanaf het begin van de tekenreeks.
Vergeet niet op Ctrl+Shift+Enter te drukken om de bovenstaande formule te gebruiken.
Er zit niets anders op dan dit allemaal op een rijtje te zetten.
Nummer uit twee teksten halen
Zodra we de startpositie van het nummergedeelte hebben en het begin van het tekstgedeelte daarna, gebruiken we gewoon de MID-functie om het gewenste nummergedeelte te extraheren.
| 1 | =MID(B3,C3,D3-C3) |
Andere methode:
Zonder het in meer detail uit te leggen, kun je ook de onderstaande complexe formule gebruiken om het getal uit een string te halen.
| 1 | =SOMPRODUCT(MID(0&B3,LARGE(INDEX(ISGETAL(--MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))*ROW(INDIRECT("1:"&LEN(B3) )),0),RIJ(INDIRECT("1:"&LEN(B3))))+1,1)*10^RIJ(INDIRECT("1:"&LEN(B3)))/10) |
Houd er rekening mee dat deze formule hierboven u 0 geeft als er geen getal in de tekenreeks wordt gevonden en dat voorloopnullen worden weggelaten.
Zoek en extraheer nummer van string naar tekst in Google Spreadsheets
De bovenstaande voorbeelden werken in Google Spreadsheets op dezelfde manier als in Excel.
