Splošna formula
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Povzetek
Če želite črke v nizu prevesti v številke, lahko uporabite matrično formulo, ki temelji na funkcijah TEXTJOIN in VLOOKUP, z določeno tabelo prevajanja, ki zagotavlja potrebna iskanja. V prikazanem primeru je formula v C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
kjer je "xtable" imenovani obseg E5: F10.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
V bistvu ta formula uporablja matrično operacijo za generiranje nabora črk iz vhodnega besedila, vsako črko posebej prevede v številko, nato ponovno združi vse številke in izhod vrne kot niz.
Za razčlenitev vhodnega niza v matriko ali črke uporabimo funkcije MID, ROW, LEN in INDIRECT, kot je ta:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN vrne dolžino vhodnega besedila, ki je povezano v "1:" in je posredovano INDIRECT kot besedilo. INDIRECT besedilo oceni kot sklic na vrstico, funkcija ROW pa vrne niz števil v MID:
MID(B5,(1;2;3),1)
Nato MID izvleče en znak za vsak začetni položaj in imamo:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
V bistvu prosimo VLOOKUP, da hkrati najde ujemanje za "a", "b" in "c". Zaradi nejasnih razlogov moramo to polje na poseben način "razporediti" z uporabo funkcij T in IF. Po zagonu VLOOKUP imamo:
=TEXTJOIN("",1,(9;4;6))
in TEXTJOIN vrne niz "946".
Izpišite številko
Če želite številko izpisati kot končni rezultat (namesto niza), dodajte nič. Matematična operacija bo niz prisilila v število.
Vsote števil
Če želite seštevati številke, namesto da jih navedete, lahko TEXTJOIN nadomestite s SUM tako:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Opomba: funkcija TEXTJOIN je bila prek naročninskega programa Office 365 uvedena leta 2018.
