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
(E-Mail Removed)

On Tue, 28 Jun 2005 13:35:02 -0700, "jplazola"
<(E-Mail Removed)> 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
>(E-Mail Removed)

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

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
> (E-Mail Removed)

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
> (E-Mail Removed)

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
> (E-Mail Removed)