Convert Number into Text

[Guest]

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

 

[Vasant Nanavati]

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.

 

[Guest]

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






.

 

[Frank Kabel]

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