How to convert number into text values in Excel 2

[imtiyaz]

Hi,
I have a problem of converting ( in Excel sheet) all amount in numbers to its text type. say column A is having rows with value $100, $1200, $10000 etc then i want to get text in next column B $ One Hundred only, $ One thousand two hundred only, $ Ten thousand only.
Is it possible with some function available in MS Excel?
Thanks in anticipation,
Imtiyaz

Happy new year to all.....

 

[DaleWatson123321]

imtiyaz,

Here's a function which works perfectly.

Once you copy the following code to a VBA module in your file, then you would use the "custom function" by simply typing =spellnumber(A1).

I need to point out that this is NOT my example. I would give credit to whoever created it, by I can't recall exactly where it came from.

I expect this is what you are looking for. If you have any problem copying the code into your file, you could choose to email me, and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

=================
START OF CODE
=================

Option Explicit

'****************
' Main Function *
'****************
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".&quot
'Convert cents and set MyNumber to dollar amount
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> &quot;&quot;
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> &quot;&quot; Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = &quot;&quot;
End If
Count = Count + 1
Loop

Select Case Dollars
Case &quot;&quot;
Dollars = &quot;No Dollars&quot;
Case &quot;One&quot;
Dollars = &quot;One Dollar&quot;
Case Else
Dollars = Dollars & &quot; Dollars&quot;
End Select

Select Case Cents
Case &quot;&quot;
Cents = &quot; and No Cents&quot;
Case &quot;One&quot;
Cents = &quot; and One Cent&quot;
Case Else
Cents = &quot; and &quot; & Cents & &quot; Cents&quot;
End Select

SpellNumber = Dollars & Cents
End Function

'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right(&quot;000&quot; & MyNumber, 3)

'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> &quot;0&quot; Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & &quot; Hundred &quot;
End If

'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> &quot;0&quot; Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String

Result = &quot;&quot; 'null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select Case Val(TensText)
Case 10: Result = &quot;Ten&quot;
Case 11: Result = &quot;Eleven&quot;
Case 12: Result = &quot;Twelve&quot;
Case 13: Result = &quot;Thirteen&quot;
Case 14: Result = &quot;Fourteen&quot;
Case 15: Result = &quot;Fifteen&quot;
Case 16: Result = &quot;Sixteen&quot;
Case 17: Result = &quot;Seventeen&quot;
Case 18: Result = &quot;Eighteen&quot;
Case 19: Result = &quot;Nineteen&quot;
Case Else
End Select
Else ' If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = &quot;Twenty &quot;
Case 3: Result = &quot;Thirty &quot;
Case 4: Result = &quot;Forty &quot;
Case 5: Result = &quot;Fifty &quot;
Case 6: Result = &quot;Sixty &quot;
Case 7: Result = &quot;Seventy &quot;
Case 8: Result = &quot;Eighty &quot;
Case 9: Result = &quot;Ninety &quot;
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function

'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = &quot;One&quot;
Case 2: GetDigit = &quot;Two&quot;
Case 3: GetDigit = &quot;Three&quot;
Case 4: GetDigit = &quot;Four&quot;
Case 5: GetDigit = &quot;Five&quot;
Case 6: GetDigit = &quot;Six&quot;
Case 7: GetDigit = &quot;Seven&quot;
Case 8: GetDigit = &quot;Eight&quot;
Case 9: GetDigit = &quot;Nine&quot;
Case Else: GetDigit = &quot;&quot;
End Select
End Function

=================
END OF CODE
=================

 

[ETID]

the text() formula has many formats that you can mix


if your currency ia in col A then in col B the formula would be:


=TEXT(A1,&quot;######.00&quot or =TEXT(A1,&quot;######&quotadd ad many &quot;#&quot; as you need, (1 for each whole digit)

fill down

 

[DaleWatson123321]

mtiyaz, and ETID,

I think, based on the description, that &quot;mtiyaz&quot; is looking for a function which will &quot;spell out&quot; the number - i.e. $1234 would be &quot;$ One Thousand Two Hundred and Thirty Four Dollars Only&quot;.

In the solution I posted, I should have suggested that &quot;mtiyaz&quot; enter the custom function as follows:

=&quot;$ &quot;&spellnumber(A1)&&quot; Only&quot;

...which will result in adding the &quot;$ before, and &quot;Only&quot; after the spelled-out number.

Hope this helps. Please advise. (And if you could use a copy of the file, don't hesitate to email me, and I'll send the file via return email.)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ETID]

Oh, I see, duh...I guess I should read slower

 

[imtiyaz]

Thanks DaleWatson123321
I am sending a excel file.
thanks once again
imtiyaz