Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to convert numbers to corresponding letters? Ex: 123 to abc
I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers to letters is as follows: 1 = A 6 = F 2 = B 7 = G 3 = C 8 = H 4 = D 9 = I 5 = E 0 = J For example, I'd like to convert a cell that contains '1250' to 'ABEJ' I've had no success with the HELP feature in Excel. Thank you for any help you may provide Jason |
#2
|
|||
|
|||
On Tue, 28 Jun 2005 13:35:02 -0700, "jplazola"
wrote: I am trying to find a formula that will convert existing number combinations into corresponding letter combinations in Excel. The relationship of numbers to letters is as follows: 1 = A 6 = F 2 = B 7 = G 3 = C 8 = H 4 = D 9 = I 5 = E 0 = J For example, I'd like to convert a cell that contains '1250' to 'ABEJ' I've had no success with the HELP feature in Excel. Thank you for any help you may provide Jason The problem is concatenating the resultant array of letters. One solution to that problem is to go to http://xcell05.free.fr/english/. and download and install Longre's free morefunc.xll add-in. Then use the MCONCAT function in this *array* formula: =MCONCAT(CHOOSE(1+MID(A1,ROW(INDIRECT( "1:"&LEN(A1))),1),"J","A","B","C","D","E","F","G", "H","I")) To enter an array formula, after typing or pasting it in, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#3
|
|||
|
|||
try playing with the CODE and CHAR functions.
Eddie O "jplazola" wrote: I am trying to find a formula that will convert existing number combinations into corresponding letter combinations in Excel. The relationship of numbers to letters is as follows: 1 = A 6 = F 2 = B 7 = G 3 = C 8 = H 4 = D 9 = I 5 = E 0 = J For example, I'd like to convert a cell that contains '1250' to 'ABEJ' I've had no success with the HELP feature in Excel. Thank you for any help you may provide Jason |
#4
|
|||
|
|||
fist make a table with your conversion data
then if all of the number cominations are 4 long =vlookup(value(mid(number,1,1)), table,2)&vlookup(value(mid(number,2,1)),table,2)&v lookup(value(mid(number,3,1)),table,2)&vlookup(val ue(mid(number,4,1)),table,2) if you have varying length of number combinations you can make if more complex by adding if(len() levels "jplazola" wrote: I am trying to find a formula that will convert existing number combinations into corresponding letter combinations in Excel. The relationship of numbers to letters is as follows: 1 = A 6 = F 2 = B 7 = G 3 = C 8 = H 4 = D 9 = I 5 = E 0 = J For example, I'd like to convert a cell that contains '1250' to 'ABEJ' I've had no success with the HELP feature in Excel. Thank you for any help you may provide Jason |
#5
|
|||
|
|||
Try this:
=IF(INT(A1/1000)=0,"J",CHAR(INT(A1/1000)+64))&IF(INT(MOD(A1,1000)/100)=0,"J",CHAR(INT(MOD(A1,1000)/100)+64))&IF(INT(MOD(A1,100)/10)=0,"J",CHAR(INT(MOD(A1,100)/10)+64))&IF(MOD(A1,10)=0,"J",CHAR(MOD(A1,10)+64)) Only works for 4 digit numbers HTH "jplazola" wrote: I am trying to find a formula that will convert existing number combinations into corresponding letter combinations in Excel. The relationship of numbers to letters is as follows: 1 = A 6 = F 2 = B 7 = G 3 = C 8 = H 4 = D 9 = I 5 = E 0 = J For example, I'd like to convert a cell that contains '1250' to 'ABEJ' I've had no success with the HELP feature in Excel. Thank you for any help you may provide Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |