Download het voorbeeldwerkboek
Deze tutorial zal laat u zien hoe u tekst en cijfers kunt scheiden van een alfanumerieke tekenreeks in Excel en Google Spreadsheets.
Scheid nummer en tekst van string
In dit artikel wordt besproken hoe u getallen en tekst kunt splitsen als u alfanumerieke gegevens hebt waarbij het eerste deel tekst is en het laatste deel numeriek (of omgekeerd). Zie voor meer complexe gevallen het artikel Niet-numerieke tekens verwijderen.
Nummer van rechts extraheren
Het gemakkelijkste geval om getallen uit een string te extraheren, is wanneer het getal aan het rechteruiteinde van die string te vinden is. Eerst zoeken we de beginpositie van het nummer met de FIND-functie en extraheren we deze met de RIGHT-functie.
| 1 | =RIGHT(B3,LEN(B3)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))+1) |
Laten we de bovenstaande formule doornemen.
Zoek eerste nummer
We kunnen de FIND-functie gebruiken om de startpositie van het nummer te lokaliseren.
| 1 | =MIN(VINDEN({0,1,2,3,4,5,6,7,8,9},B3&"0123456789")) |
Voor het argument find_text van de FIND-functie gebruiken we de arrayconstante {0,1,2,3,4,5,6,7,8,9}, waardoor de FIND-functie afzonderlijke zoekopdrachten uitvoert voor elke waarde in de array constante.
Het argument binnen_tekst van de functie FIND is celwaarde & "0123456789". In ons voorbeeld "maandag010123456789".
Aangezien de matrixconstante 10 getallen bevat, is het resultaat een matrix van 10 waarden. In ons voorbeeld: {7,8,11,12,13,14,15,16,17,18}. Dan zoeken we gewoon naar het minimum aantal posities binnen deze array en krijgen dus de plaats van het eerste nummer.
Uittreksel Nummer Onderdeel
Zodra we de startpositie hebben van het nummer aan het einde van onze alfanumerieke reeks, kunnen we de functie RECHTS gebruiken om het te extraheren.
| 1 | =RECHTS(B3,LENGTE(B3)-C3+1) |
Tekstgedeelte extraheren
Met de startpositie van het nummergedeelte kunnen we tegelijkertijd het einde van het tekstgedeelte bepalen. We kunnen de LEFT-functie gebruiken om het uit te pakken.
| 1 | =LINKS(B3,C3-1) |
Een ingewikkelder geval van het extraheren van getallen uit een string is wanneer het nummer aan het begin (d.w.z. de linkerkant) van de string kan worden gevonden. Het is duidelijk dat u niet de startpositie hoeft te vinden, maar de positie waar deze eindigt. Eerst vinden we de positie van het laatste nummer met behulp van de SUBSTITUTE-functie en extraheren we het nummer met de LEFT-functie.
| 1 | =LEFT(B3,SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{"0","1","2","3","4","5","6","7" ,"8","9"},"")))) |
Laten we de bovenstaande formule doornemen.
Vind laatste nummer
Met de SUBSTITUTE-functie kun je elk cijfer één voor één vervangen door een lege string en dan optellen hoe vaak je dat moest doen.
| 1 | =SUM(LEN(B3)-LEN(VERVANG(B3,{"0","1","2","3","4","5","6","7","8" ,"9"},""))) |
Als je elk getal één voor één vervangt door een lege tekenreeks, krijg je telkens een tekenreeks waarvan de lengte één minder is dan de oorspronkelijke lengte. In ons geval is de lengte van 1-maandag en 0-maandag beide 8. Door deze lengte af te trekken van de oorspronkelijke lengte (9 in ons geval), krijg je altijd 1. Als je deze optelt, krijg je de positie van je laatste getal .
Uittreksel Nummer Onderdeel
Zodra we de laatste positie van het nummer hebben gevonden aan het begin van onze alfanumerieke reeks, kunnen we de LEFT-functie gebruiken om het te extraheren.
| 1 | =LINKS(B3,C3) |
Tekstgedeelte extraheren
Met de laatste positie van het nummer aan het begin van onze alfanumerieke reeks, hebben we al de startpositie van ons tekstgedeelte en kunnen we de RECHTS-functie gebruiken om het te extraheren.
| 1 | =RECHTS(B3,LENGTE(B3)-C3) |
Scheid tekst en cijfers in Google Spreadsheets
Alle hierboven uitgelegde voorbeelden werken hetzelfde in Google-bladen als in Excel.
