Replacing number digits with letter code

marcd

New Member
Joined
Aug 22, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I want to replace number with coded letter.
0=Q
1=L
2=T
3=M
4=F
5=V
6=S
7=R
8=K
9=G

I may enter a 5 digits to 12 digits number and needs to get the according letters.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,013
Office Version
  1. 365
Platform
  1. Windows
With your number in A1, try this formula in B1, fill down as needed.

=CONCAT(MID("QLTMFVSRKG",1+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),1))

You might need to confirm it as a legacy array with Ctrl Shift Enter.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel.
I created helper columns from column E and filled across:
Book2
ABCDE
1CodesNumberConvert
20Q2310TMLQT
31L
42T
53M
64F
75V
86S
97R
108K
119G
Sheet1
Cell Formulas
RangeFormula
D2D2=CONCAT(E2:P2)
E2E2=IFERROR(INDEX($B$2:$B$11,1+MID($C2,COLUMN()-4,1),1),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

If you have the SEQUENCE function in your Excel 365, you could also try this non-volatile version.

20 08 23.xlsm
AB
156982VSGKT
2236598012001TMSVGKQLTQQL
Encode Number
Cell Formulas
RangeFormula
B1:B2B1=CONCAT(MID("QLTMFVSRKG",MID(A1,SEQUENCE(LEN(A1)),1)+1,1))
 

marcd

New Member
Joined
Aug 22, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you very much, it's working lovely and helps me a lot.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top