Generieke formule
(=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,"")))
Samenvatting
Om niet-numerieke tekens uit een tekstreeks te verwijderen, kunt u deze experimentele formule proberen op basis van de TEXTJOIN-functie, een nieuwe functie in Excel 2019. In het getoonde voorbeeld is de formule in C5:
(=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,"")))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
Werkend van binnen naar buiten, wordt de MID-formule gebruikt om de tekst in B5 te extraheren, één teken tegelijk. De sleutel is het RIJ / INDIRECTE stuk:
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. Verander naargelang uw gegevens.
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:
("1"; "0"; "0"; ""; "a"; "p"; "p"; "l"; "e"; "s"; ""; ""; ""; " "…)
(extra items in de array verwijderd voor leesbaarheid)
Aan deze array voegen we nul toe. Dit is een eenvoudige truc die Excel dwingt om tekst naar een getal te dwingen. Numerieke tekstwaarden zoals "1", "2", "3", "4" enz. Worden geconverteerd, terwijl niet-numerieke waarden mislukken en een #VALUE-fout veroorzaken. We gebruiken de IFERROR-functie om deze fouten op te vangen en een lege string ("") te retourneren, terwijl numerieke waarden doorgaan in de array. Het resultaat is een array die alleen cijfers en lege strings bevat:
(1; 0; 0; ""; ""; ""; ""; "";….)
Ten slotte gaat dit matrixresultaat 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.
Opmerking: TEXTJOIN retourneert de getallen als tekst, bijvoorbeeld "100," 500 ", enz. Als u een echt numeriek resultaat wilt, voegt u nul toe of plaatst u de hele formule in de functie WAARDE.
