Generisk formel
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Resumé
For at oversætte bogstaver i en streng til tal kan du bruge en matrixformel baseret på funktionerne TEXTJOIN og VLOOKUP med en defineret oversættelsestabel for at give de nødvendige opslag. I det viste eksempel er formlen i C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
hvor "xtable" er det navngivne område E5: F10.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
I kernen bruger denne formel en matrixoperation til at generere en matrix af bogstaver fra inputteksten, oversætter hvert bogstav individuelt til et tal og sammenføjer derefter alle tal igen og returnerer output som en streng.
For at parsere inputstrengen i en matrix eller bogstaver bruger vi MID-, ROW-, LEN- og INDIRECT-funktioner som denne:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returnerer længden af inputteksten, som er sammenkædet til "1:" og afleveret til INDIRECT som tekst. INDIRECT evaluerer teksten som en rækkehenvisning, og ROW-funktionen returnerer et array med tal til MID:
MID(B5,(1;2;3),1)
MID udtrækker derefter et tegn for ved hver startposition, og vi har:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
I det væsentlige beder vi VLOOKUP om at finde et match for "a", "b" og "c" på samme tid. Af uklare grunde er vi nødt til at "dereference" dette array på en bestemt måde ved hjælp af både T- og IF-funktionerne. Efter VLOOKUP-kørsler har vi:
=TEXTJOIN("",1,(9;4;6))
og TEXTJOIN returnerer strengen "946".
Output et nummer
Hvis du vil sende et tal som det endelige resultat (i stedet for en streng), skal du tilføje nul. Matematikoperationen tvinger strengen til et tal.
Summenumre
For at sammenfatte numrene i stedet for at angive dem kan du erstatte TEXTJOIN med SUM således:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Bemærk: TEXTJOIN-funktionen blev introduceret via Office 365-abonnementsprogrammet i 2018.
