Compound Formula for Substitution

[Chris]

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

 

[T. Valko]

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/3000-2077_4-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.

 

[T. Valko]

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.

 

[Jacob Skaria]

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