Replace / Substitute formula required - File attached

[aadii]

File Attached - how to Replace Text to Number in a word or multiple words
Thanks in advance

Number

Alphabet

1

A I J Q Y

QUE -

I want to Replace these characters and give numeric value whenever comes in a word

2

B K R

3

C G L S

eg

bat

should return

214​

4

D M T

bat

B

2​

5

E H N X

A

1​

6

U V W

T

4​

7

O Z

how to make these formula

8

P F

9

No Alphabet

 

[navic]

Try

in the 'K8' cell (copy down)

=IF(UPPER(MID($J$8,ROW(A1),1))="",0,UPPER(MID($J$8,ROW(A1),1)))

in the 'L8' cell ARRAY formula (copy down)

=IFERROR(INDEX($D$5:$D$13,MATCH(TRUE,ISNUMBER(SEARCH("*"&K8&"*","*"&$E$5:$E$13&"*")),0)),"")

in the 'M7' cell

=TRIM(CONCATENATE(L8,L9,L10,L11,L12,L13))

or

=VALUE(TRIM(CONCATENATE(L8,L9,L10,L11,L12,L13)))

 

[bosco_yip]

1] Criteria housed In Column G

2.1] Using CONCAT function (available in Office 365)

In H5, formula copied down :

=CONCAT(INDEX(MATCH("*"&MID(G5,ROW(INDIRECT("1:"&LEN(G5))),1)&"*",$E$5:$E$12,0),0))

or,

2.2] If you do not have Office 365, you could use this longer formula for all Excel versions

=SUMPRODUCT(INDEX(MATCH("*"&MID(G5,ROW(INDIRECT("1:"&LEN(G5))),1)&"*",$E$5:$E$12,0),0)*10^(LEN(G5)-ROW(INDIRECT("1:"&LEN(G5)))))




Regards