How to convert numbers to corresponding letters? Ex: 123 to abc

[jplazola]

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
[email protected]

[Eddie O]

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
> [email protected]

[Ron Rosenfeld]

On Tue, 28 Jun 2005 13:35:02 -0700, "jplazola"
<[email protected]> 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
>[email protected]

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

[bj]

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)&vlookup(value(mid(number,3,1)),table,2)&vlookup(value(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
> [email protected]

[Fred]

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
> [email protected]

[LanreyBm]

Jplazola: Consider using the function as described below.

=SUBSTITUTE(ADDRESS(1,,4),1,"")$AM1 where $AM1 is the cell that contain the number 1,2,3 or so you want to convert to A,B,C or so.