Generieke formule
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Samenvatting
Om letters in een tekenreeks naar getallen te vertalen, kunt u een matrixformule gebruiken die is gebaseerd op de TEXTJOIN- en VERT.ZOEKEN-functies, met een gedefinieerde vertaaltabel om de nodige zoekacties te bieden. In het getoonde voorbeeld is de formule in C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
waarbij "xtable" het benoemde bereik E5: F10 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
In de kern gebruikt deze formule een matrixbewerking om een reeks letters uit de invoertekst te genereren, vertaalt elke letter afzonderlijk naar een getal, voegt vervolgens alle getallen weer samen en retourneert de uitvoer als een tekenreeks.
Om de invoerstring in een array of letters te parseren, gebruiken we MID-, ROW-, LEN- en INDIRECT-functies als volgt:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN geeft de lengte van de invoertekst terug, die wordt samengevoegd tot "1:" en als tekst aan INDIRECT wordt overgedragen. INDIRECT evalueert de tekst als een rijverwijzing en de functie RIJ retourneert een reeks getallen naar MID:
MID(B5,(1;2;3),1)
MID extraheert dan een karakter voor elke startpositie en we hebben:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
In wezen vragen we VERT.ZOEKEN om tegelijkertijd een overeenkomst te vinden voor "a", "b" en "c". Om onduidelijke redenen moeten we deze array op een bepaalde manier "dereferentie" maken door zowel de T- als de IF-functies te gebruiken. Nadat VERT.ZOEKEN is uitgevoerd, hebben we:
=TEXTJOIN("",1,(9;4;6))
en TEXTJOIN geeft de tekenreeks "946" terug.
Voer een nummer uit
Om een getal als eindresultaat uit te voeren (in plaats van een string), voegt u nul toe. De wiskundige bewerking zal de string in een getal dwingen.
Som nummers
Om de getallen bij elkaar op te tellen in plaats van ze op te sommen, kunt u TEXTJOIN als volgt vervangen door SUM:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Let op: de TEXTJOIN-functie is geïntroduceerd via het Office 365-abonnementsprogramma in 2018.
