Convert Number into Text

In one column, I have numbers (120-1200). In another
column, I would like corresponding Alphabetical
representaion of the numbers.

Eg A1 = 123.56 then B1 should be ABC.EF

Thank you for your help.
Ria

I think this would be difficult to do with a formula. The following (crude
and lightly-tested) UDF may get you started.

Function NumbersToText(dNumber As Double)
If Not IsNumeric(dNumber) Then Exit Function
Dim i As Integer, sMid As String
For i = 1 To Len(CStr(dNumber))
sMid = Mid(CStr(dNumber), i, 1)
If sMid = "." Then
NumbersToText = NumbersToText & "."
ElseIf sMid = "0" Then
NumbersToText = NumbersToText & "J"
Else
NumbersToText = NumbersToText & Chr(sMid + 64)
End If
Next
End Function

Copy and paste this into a standard module, then enter:

=NumbersToText(A1)

into cell B1.

Two warnings:

1. You haven't said how you want to treat 0; I have translated it to "J".

2. The text will be based on the actual number in cell A1, which may not
necessarily be the same as the displayed amount.

--

Vasant



wrote in message
...
In one column, I have numbers (120-1200). In another
column, I would like corresponding Alphabetical
representaion of the numbers.

Eg A1 = 123.56 then B1 should be ABC.EF

Thank you for your help.
Ria

Thankyou much...that was very helpful.
-----Original Message-----
I think this would be difficult to do with a formula. The
following (crude
and lightly-tested) UDF may get you started.

Function NumbersToText(dNumber As Double)
If Not IsNumeric(dNumber) Then Exit Function
Dim i As Integer, sMid As String
For i = 1 To Len(CStr(dNumber))
sMid = Mid(CStr(dNumber), i, 1)
If sMid = "." Then
NumbersToText = NumbersToText & "."
ElseIf sMid = "0" Then
NumbersToText = NumbersToText & "J"
Else
NumbersToText = NumbersToText & Chr(sMid + 64)
End If
Next
End Function

Copy and paste this into a standard module, then enter:

=NumbersToText(A1)

into cell B1.

Two warnings:

1. You haven't said how you want to treat 0; I have
translated it to "J".

2. The text will be based on the actual number in cell
A1, which may not
necessarily be the same as the displayed amount.

--

Vasant



wrote in message
...
In one column, I have numbers (120-1200). In another
column, I would like corresponding Alphabetical
representaion of the numbers.

Eg A1 = 123.56 then B1 should be ABC.EF

Thank you for your help.
Ria


.

Hi
see for some more links
http://www.xldynamic.com/source/xld.xlFAQs.html



--
Regards
Frank Kabel
Frankfurt, Germany

schrieb im Newsbeitrag
...
In one column, I have numbers (120-1200). In another
column, I would like corresponding Alphabetical
representaion of the numbers.

Eg A1 = 123.56 then B1 should be ABC.EF

Thank you for your help.
Ria