If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do you convert lower case text to upper case text in Excel? | nicole134 | General Discussion | 3 | September 9th, 2004 06:59 PM |
Converting imported text into number format | Andy B | Worksheet Functions | 1 | May 20th, 2004 02:36 PM |
Fuction that convert number to English text | oraluck p | Worksheet Functions | 4 | May 13th, 2004 01:30 PM |
Vertical Text Orientation | Paul Anderson [MSFT] | Visio | 0 | May 11th, 2004 05:16 PM |
convert numbers to text | bMunny | Worksheet Functions | 6 | December 5th, 2003 07:50 PM |