Generisk formel
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Sammanfattning
För att översätta bokstäver i en sträng till siffror kan du använda en matrisformel baserad på TEXTJOIN- och VLOOKUP-funktionerna, med en definierad översättningstabell för att ge de nödvändiga sökningarna. I exemplet som visas är formeln i C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
där "xtable" är det namngivna intervallet E5: F10.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
I grunden använder denna formel en array-operation för att generera en array med bokstäver från inmatningstexten, översätter varje bokstav individuellt till ett nummer och sammanfogar sedan alla siffror igen och returnerar utdata som en sträng.
För att analysera ingångssträngen i en matris eller bokstäver använder vi MID, ROW, LEN och INDIRECT-funktioner så här:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returnerar längden på den inmatade texten, som sammanlänkas till "1:" och överlämnas till INDIRECT som text. INDIRECT utvärderar texten som en radreferens, och ROW-funktionen returnerar en matris med siffror till MID:
MID(B5,(1;2;3),1)
MID extraherar sedan ett tecken för vid varje startposition och vi har:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
I huvudsak ber vi VLOOKUP att hitta en matchning för "a", "b" och "c" samtidigt. Av dunkla skäl måste vi "dereferera" denna array på ett visst sätt med både T- och IF-funktionerna. Efter VLOOKUP-körningar har vi:
=TEXTJOIN("",1,(9;4;6))
och TEXTJOIN returnerar strängen "946".
Mata ut ett nummer
För att mata ut ett tal som slutresultat (istället för en sträng), lägg till noll. Matematikoperationen tvingar strängen till ett tal.
Summanummer
För att summera siffrorna istället för att lista dem kan du ersätta TEXTJOIN med SUM så här:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Obs! TEXTJOIN-funktionen introducerades via Office 365-prenumerationsprogrammet 2018.
