Üldine valem
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Kokkuvõte
Stringis olevate tähtede numbriteks tõlkimiseks võite vajalike otsingute pakkumiseks kasutada massiivivalemit, mis põhineb funktsioonidel TEXTJOIN ja VLOOKUP koos määratletud tõlketabeliga. Näidatud näites on valem C5-s:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
kus "xtable" on nimega vahemik E5: F10.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Põhimõtteliselt kasutab see valem massiivoperatsiooni tähtede massiivi loomiseks sisendtekstist, tõlgib iga tähe eraldi numbriks, ühendab seejärel kõik numbrid uuesti ja tagastab väljundi stringina.
Sisendstringi massiiviks või tähtedeks sõelumiseks kasutame funktsioone MID, ROW, LEN ja INDIRECT järgmiselt:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN tagastab sisestatud teksti pikkuse, mis on liidetud tähega "1:" ja mis antakse tekstina INDIRECT-ile. INDIRECT hindab teksti reaviitena ja funktsioon ROW tagastab arvude massiivi MID-le:
MID(B5,(1;2;3),1)
Seejärel eraldab MID igas lähtepositsioonis ühe märgi ja meil on:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Sisuliselt palume VLOOKUPil leida vaste samaaegselt tähtedele "a", "b" ja "c". Hämaratel põhjustel peame sellest massiivist teatud viisil "kõrvale jätma", kasutades nii funktsioone T kui ka IF. Pärast VLOOKUPi käivitamist on meil:
=TEXTJOIN("",1,(9;4;6))
ja TEXTJOIN tagastab stringi "946".
Väljenda number
Numbri väljastamiseks lõpptulemusena (stringi asemel) lisage null. Matemaatikaoperatsioon sunnib stringi numbriks.
Summanumbrid
Numbrite kokkuvõtmiseks, selle asemel et neid loetleda, võite asendada TEXTJOIN järgmisega:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Märkus: funktsioon TEXTJOIN võeti kasutusele Office 365 tellimisprogrammi kaudu 2018. aastal.
