Generieke formule
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Samenvatting
Om numerieke tekens uit een tekstreeks te verwijderen, kunt u een formule gebruiken die is gebaseerd op de TEXTJOIN-functie. In het getoonde voorbeeld is de formule in C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Uitleg
Excel heeft geen manier om de letters in een tekstreeks rechtstreeks in een formule naar een array te casten. Als tijdelijke oplossing gebruikt deze formule de MID-functie, met hulp van de ROW- en INDIRECT-functies om hetzelfde resultaat te bereiken. De formule in C5, gekopieerd, is:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Dit ziet er behoorlijk ingewikkeld uit, maar de essentie is dat we een reeks van alle tekens in B5 maken en elk teken testen om te zien of het een getal is. Als dit het geval is, verwijderen we de waarde en vervangen we deze door een lege string (""). Zo niet, dan voegen we het niet-numerieke teken toe aan een "verwerkte" array. Ten slotte gebruiken we de TEXTJOIN-functie (nieuw in Excel 2019) om alle tekens samen te voegen en lege waarden te negeren.
Door van binnen naar buiten te werken, wordt de MID-functie gebruikt om de tekst in B5 te extraheren, teken voor teken. De sleutel is het ROW- en INDIRECT-fragment hier:
ROW(INDIRECT("1:100"))
die een array met 100 getallen op de volgende manier laat draaien:
(1,2,3,4,5,6,7,8… .99,100)
Opmerking: 100 staat voor het maximale aantal te verwerken tekens. Pas de aanpassing aan uw gegevens aan, of gebruik de LEN-functie zoals hieronder wordt uitgelegd.
Deze array gaat naar de MID-functie als het argument start_num . Voor num_chars gebruiken we 1.
De MID-functie retourneert een array zoals deze:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Opmerking: extra items in de array zijn verwijderd voor leesbaarheid.
Aan deze array voegen we nul toe. Dit is een simpele truc die Excel dwingt om tekst naar een getal te dwingen. Numerieke tekstwaarden zoals "1", "2", "3", "4" enz. Worden zonder fouten geconverteerd, maar niet-numerieke waarden mislukken en geven een #VALUE-fout. We gebruiken de ALS-functie met de ISERR-functie om deze fouten op te vangen. Als we een fout zien, weten we dat we een niet-numeriek teken hebben, dus we brengen dat teken in de verwerkte array met een andere MID-functie:
MID(B5,ROW(INDIRECT("1:100")),1)
Als er geen foutmelding wordt gegeven, weten we dat we een getal hebben, dus voegen we een lege string ("") in de array in plaats van het getal in.
Het uiteindelijke array-resultaat gaat naar de TEXTJOIN-functie als het text1-argument. Als scheidingsteken gebruiken we een lege string ("") en voor ignore_empty leveren we TRUE. TEXTJOIN voegt vervolgens alle niet-lege waarden in de array samen en retourneert het resultaat.
Nauwkeurige array-lengte
In plaats van een getal als 100 hard te coderen in INDIRECT, kunt u de LEN-functie gebruiken om een array te bouwen met het werkelijke aantal tekens in de cel als volgt:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN retourneert het aantal tekens in de cel als een getal, dat wordt gebruikt in plaats van 100. Hierdoor kan de formule automatisch worden vergroot tot een willekeurig aantal tekens.
Extra ruimte verwijderen
Als u numerieke tekens verwijdert, kan het zijn dat er extra spaties overblijven. Om voorloop- en volgspaties te verwijderen en spaties tussen woorden te normaliseren, kunt u de formule op deze pagina in de TRIM-functie plaatsen:
=TRIM(formula)
Met SEQUENCE
In Excel 365 kan de nieuwe SEQUENCE-functie de bovenstaande ROW + INDIRECTE code vervangen:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Hier gebruiken we SEQUENCE + LEN om in één stap een array van de juiste lengte te bouwen.
Met LET
We kunnen deze formule verder stroomlijnen met de LET-functie. Omdat de array tweemaal hierboven is gemaakt met SEQUENCE en LEN, kunnen we array definiëren als een variabele en deze slechts één keer maken:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Hier wordt de waarde van de array slechts één keer ingesteld en vervolgens twee keer gebruikt in de MID-functie.
