# Replace Numbers with Letter

#### S P P

##### Member
Goodnight!

Replace Numbers with Letter

#### Attachments

• 10.9 KB Views: 14

#### bosco_yip

##### Excel Ninja
Try,

In D3, array (CSE) formula copied down :

=SUBSTITUTE(TRIM(SUBSTITUTE(TEXTJOIN("",,TEXT(SUBSTITUTE(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),\$E\$1:\$N\$1,\$E\$2:\$N\$2),";;;@")),"."," "))," ",",")

#### Attachments

• 12.1 KB Views: 4

#### S P P

##### Member
bosco_yip

Not translating TEXTJOIN

Can you replace

INDEX & MATCH or the function VLOOKUP

#### bosco_yip

##### Excel Ninja
bosco_yip

Not translating TEXTJOIN

Can you replace

INDEX & MATCH or the function VLOOKUP
Then, use the old day method,

In D3 formula copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,E\$2),2,F\$2),3,G\$2),4,H\$2),5,I\$2),6,J\$2),7,K\$2),8,L\$2),9,M\$2),0,N\$2),".",",")

Edit : the above formula has fixed (in replacing 0 with "X" as per the OP's comment)

Regards

Last edited:

#### S P P

##### Member
bosco_yip

Worked perfectly

Thank you very much

#### S P P

##### Member
bosco_yip

Zero is not replacing with X

bosco_yip
Stays like this

\$125,30 UDC,T

\$120,30 UDX,T

#### bosco_yip

##### Excel Ninja
bosco_yip

Zero is not replacing with X
Sorry, my mistake,

here is the fixed of the formula :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,E\$2),2,F\$2),3,G\$2),4,H\$2),5,I\$2),6,J\$2),7,K\$2),8,L\$2),9,M\$2),0,N\$2),".",",")

Regards

#### S P P

##### Member
bosco_yip

keep it up

The end X is missing, but before the end it replaces

\$125,30 UDC,T

\$120,30 UDX,T

#### bosco_yip

##### Excel Ninja
bosco_yip

keep it up

The end X is missing, but before the end it replaces

\$125,30 UDC,T

\$120,30 UDX,T
Then,

Use this formula as replacement :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B3,"#.00"),1,E\$2),2,F\$2),3,G\$2),4,H\$2),5,I\$2),6,J\$2),7,K\$2),8,L\$2),9,M\$2),0,N\$2),".",",")

#### deciog

##### Active Member
SPP
For the Brazilian Portuguese version of the formula, you have to change the format of the TEXT function, where this "TEXT(B3,"#.00")" changes to "TEXTO(B3;"#,00")", Comma

Hope this helps

Hugs
decio

#### S P P

##### Member
bosco_yip

TEXT(B3,"#.00")
I just needed to change the semicolon
TEXT(B3,"#,00")
Worked perfectly

Thanks a lot for the help

deciog

Muito obrigado por participar deste tópico
Já tinha feito a substituição, mas mesmo assim valeu.