I need to try to create a formula to convert numbers to letters using the
following scale:

0 = A
1 = B
2 = C
3 = D
4 = E
5 = F
6 = G
7 = H
8 = I
9 = J

Such that 12.34 would be transformed to BCDE.

This is the formula I had tried, but it won't accept:

=CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2))

M2 is my starting number and the table array is the Number/Letter Conversion
Chart.

I truly hope someone can help me.

Thank you,
Chris

If you can download and install the free add-in Morefunct.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use an array formula** like this:

=MCONCAT(LOOKUP(--MID(SUBSTITUTE(C1,".",""),ROW(INDIRECT("1:"&LEN(C1)-COUNT(FIND(".",C1)))),1),A1:B10))

Where:
C1 = some number
A1:B10 = lookup table

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Chris" <(E-Mail Removed)> wrote in message
news:A251E1E6-BDD3-4C4E-BE59-(E-Mail Removed)...
>I need to try to create a formula to convert numbers to letters using the
> following scale:
>
> 0 = A
> 1 = B
> 2 = C
> 3 = D
> 4 = E
> 5 = F
> 6 = G
> 7 = H
> 8 = I
> 9 = J
>
> Such that 12.34 would be transformed to BCDE.
>
> This is the formula I had tried, but it won't accept:
>
> =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2))
>
> M2 is my starting number and the table array is the Number/Letter
> Conversion
> Chart.
>
> I truly hope someone can help me.
>
> Thank you,
> Chris

P.S.

If your number is *always* only 4 digits then this can be done with a much
simpler formula. You should have posted several examples of the numbers
you're dealing with.

--
Biff
Microsoft Excel MVP


"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you can download and install the free add-in Morefunct.xll from:
>
> http://xcell05.free.fr/morefunc/english/index.htm
>
> Alternate download site:
>
> http://www.download.com/Morefunc/300...-10423159.html
>
> Then you can use an array formula** like this:
>
> =MCONCAT(LOOKUP(--MID(SUBSTITUTE(C1,".",""),ROW(INDIRECT("1:"&LEN(C1)-COUNT(FIND(".",C1)))),1),A1:B10))
>
> Where:
> C1 = some number
> A1:B10 = lookup table
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
> SHIFT key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:A251E1E6-BDD3-4C4E-BE59-(E-Mail Removed)...
>>I need to try to create a formula to convert numbers to letters using the
>> following scale:
>>
>> 0 = A
>> 1 = B
>> 2 = C
>> 3 = D
>> 4 = E
>> 5 = F
>> 6 = G
>> 7 = H
>> 8 = I
>> 9 = J
>>
>> Such that 12.34 would be transformed to BCDE.
>>
>> This is the formula I had tried, but it won't accept:
>>
>> =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2))
>>
>> M2 is my starting number and the table array is the Number/Letter
>> Conversion
>> Chart.
>>
>> I truly hope someone can help me.
>>
>> Thank you,
>> Chris
>
>

How about a UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

In cell A1 = 12.34
In cell B1 try the below formula
=convertdata(A1)

Function ConvertData(varData As Variant) As String
Dim intCount As Integer
For intCount = 1 To Len(varData.Value)
If IsNumeric(Mid(varData.Value, intCount, 1)) Then
ConvertData = ConvertData & Chr(65 + Mid(varData.Value, intCount, 1))
End If
Next
End Function

--
Jacob


"Chris" wrote:

> I need to try to create a formula to convert numbers to letters using the
> following scale:
>
> 0 = A
> 1 = B
> 2 = C
> 3 = D
> 4 = E
> 5 = F
> 6 = G
> 7 = H
> 8 = I
> 9 = J
>
> Such that 12.34 would be transformed to BCDE.
>
> This is the formula I had tried, but it won't accept:
>
> =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2))
>
> M2 is my starting number and the table array is the Number/Letter Conversion
> Chart.
>
> I truly hope someone can help me.
>
> Thank you,
> Chris