Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u getallen uit de tekst in een cel in Excel en Google Spreadsheets kunt verwijderen.
We bespreken twee verschillende formules voor het verwijderen van getallen uit tekst in Excel.
VERVANG Functie Formule
We kunnen een formule gebruiken op basis van de SUBSTITUTE-functie. Het is een lange formule, maar het is een van de gemakkelijkste manieren om getallen uit een alfanumerieke reeks te verwijderen.
In deze formule hebben we SUBSTITUTE-functies 10 keer genest, zoals deze:
| 1 | =VERVANG(vervanging(vervanging(vervanging(vervanging(vervanging(vervanging(vervanging(vervanging(vervanging(B3,1,""),2,""),3,""),4,""),5," "),6,""),7,""),8,""),9,""),0,"") |
Matrix TEXTJOIN-formule
Om getallen uit alfanumerieke reeksen te verwijderen, kunnen we ook een complexe matrixformule gebruiken die bestaat uit de functies TEXTJOIN, MID, ROW en INDIRECT.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Opmerking: TEXTJOIN is een nieuwe Excel-functie die beschikbaar is in Excel 2022+ en Office 365.
Dit is een complexe formule, dus we zullen het in stappen verdelen om het beter te begrijpen.
Stap 1
De MID-functie wordt gebruikt om de alfanumerieke tekenreeks te extraheren op basis van de argumenten start_num en num_chars.
Voor het start_num-argument in de MID-functie gebruiken we de resulterende arraylijst van de ROW- en INDIRECT-functies.
| 1 | =RIJ(INDIRECT("1:"&LENGTE(B3))) |
En voor het num-chars-argument plaatsen we 1. Nadat de argumenten in de MID-functie zijn geplaatst, wordt een array geretourneerd.
| 1 | {=MID(B3,RIJ(INDIRECT("1:"&LEN(B3))),1)} |
Stap 2
We voegen nul toe aan elke waarde in de resulterende array (die we krijgen van de bovenstaande MID-functie). Als we in Excel getallen aan niet-numerieke tekens toevoegen, krijgen we een #WAARDE! Fout. Dus, na het toevoegen van 0 in de bovenstaande array, krijgen we een array met getallen en #Value! Fouten.
| 1 | {=MID(B3,RIJ(INDIRECT("1:"&LENGTE(B3))),1)+0} |
Stap 3
Na het toevoegen van 0, wordt de resulterende array in de ISERR-functie geplaatst. Zoals we weten, retourneert de ISERR-functie TRUE voor fouten en FALSE voor niet-foutwaarden.
Het geeft dus een array van TRUE en FALSE, TRUE voor niet-numerieke tekens en FALSE voor getallen.
| 1 | =ISRR(MID(B3,RIJ(INDIRECT("1:"&LEN(B3))),1)+0) |
Stap 4
Nu voegen we de ALS-functie toe.
De IF-functie controleert het resultaat van de ISERR-functie (stap 3). Als de waarde WAAR is, retourneert het een array van alle tekens van een alfanumerieke tekenreeks. Hiervoor hebben we nog een MID-functie toegevoegd zonder aan het einde nul toe te voegen. Als de waarde van de ALS-functie FALSE is, wordt blanco ("") geretourneerd.
Op deze manier hebben we een array die alleen de niet-numerieke tekens van de tekenreeks bevat.
| 1 | =IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1 ),"") |
Stap 5
Ten slotte wordt de bovenstaande array in de functie TEXTJOIN geplaatst. De functie TEXTJOIN voegt alle tekens van de bovenstaande array samen en negeert de lege tekenreeks.
Het scheidingsteken voor deze functie is een lege tekenreeks ("") en de waarde van het argument negeren_leeg wordt TRUE ingevoerd.
Dit geeft ons het gewenste resultaat, d.w.z. alleen de niet-numerieke tekens van de alfanumerieke reeks.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Opmerking: dit is een matrixformule. Wanneer u matrixformules invoert in Excel 2022 of eerder, moet u gebruiken CTRL + SHIFT + ENTER om de formule in te voeren in plaats van de gewone BINNENKOMEN.
U weet dat u de formule correct hebt ingevoerd door de accolades die verschijnen. Typ de accolades NIET handmatig, de formule werkt niet.
Met Office 365 (en vermoedelijk versies van Excel na 2022) kun je de formule gewoon zoals normaal invoeren.
TRIM-functie
Als de getallen uit de tekenreeks worden verwijderd, hebben we mogelijk extra spaties over. Om alle volg- en voorloopspaties en de extra spaties tussen woorden te verwijderen, kunnen we de TRIM-functie vóór de hoofdformule gebruiken, zoals deze:
| 1 | =TRIM(C3) |
Nummers verwijderen uit tekst in Google Spreadsheets
De formule om getallen uit de tekst te verwijderen werkt in Google Spreadsheets precies hetzelfde als in Excel:
