Hi,

Pl. give refrence for converting a figure (eg. 102 ) into a text (
One hundred and two ) in excel for a accoutning work. Will macro will
be able to handle this !

Thanks

M. Azim


--
m_azim1
------------------------------------------------------------------------
m_azim1's Profile: http://www.excelforum.com/member.php...o&userid=33181
View this thread: http://www.excelforum.com/showthread...hreadid=529988

Strangely, this came up yesterday under the equally curious heading:
"Conversion of Numercials to Figure" - do a search on that in the same group
as someone gave a very complete answer to this then.

"m_azim1" wrote:


Hi,

Pl. give refrence for converting a figure (eg. 102 ) into a text (
One hundred and two ) in excel for a accoutning work. Will macro will
be able to handle this !

Thanks

M. Azim


--
m_azim1
------------------------------------------------------------------------
m_azim1's Profile: http://www.excelforum.com/member.php...o&userid=33181
View this thread: http://www.excelforum.com/showthread...hreadid=529988

Hi m_azim1,
An alternative to a macro is to use Word for this conversion. Word fields
accept \* CardText, which displays results as cardinal text. To see how this
works, go to Word.
Press Ctrl + F9 to insert a field. Between the braces, type
= 126 \* CardText \* Caps

Press Ctrl + Shift +F9 and you will see One Hundred Twenty-Six. (The *Caps
makes the first letter upper case).

You can copy/paste excel tabular data to word, and edit the field
referencing row and columns, such as
{ = SUM(A1:B2) \* CardText \* Caps } in your Word report.

I don't know of any function equivalent in Excel, but perhaps someone else
may.
Cheers,
Annie


"m_azim1" wrote:


Hi,

Pl. give refrence for converting a figure (eg. 102 ) into a text (
One hundred and two ) in excel for a accoutning work. Will macro will
be able to handle this !

Thanks

M. Azim


--
m_azim1
------------------------------------------------------------------------
m_azim1's Profile: http://www.excelforum.com/member.php...o&userid=33181
View this thread: http://www.excelforum.com/showthread...hreadid=529988

Here is a very popular bit of code from Microsoft that will convert any
number in a cell to English words. All code and text from below here is
the work of Microsoft.

Summary
This article contains sample Microsoft Visual Basic for Applications
functions that you can use to convert a numeric value into its
equivalent in English words. For example, you can change 32.50 into
"Thirty Two Dollars and Fifty Cents" by entering the following formula
into a cell:


=SpellNumber(32.50)

You can also use these functions to refer to other cells in the
workbook. For example, if the number 32.50 was in A1, you could type
the following into a cell:
=SpellNumber(A1)

The Function Wizard can also be used to enter a custom function in a
worksheet. To use the Function Wizard, follow these steps:

1. Click the Function Wizard button, and select User Defined under
Function Category.
2. Select SpellNumber, and enter your number or cell reference.
3. Click Finish

To Create the Sample Functions

1. Insert a module sheet into a workbook. To do this in Microsoft Excel
97 or Microsoft Excel 98, point to Macro on the Tools menu, and then
click Visual Basic Editor. In the Visual Basic Editor, click Module on
the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the
Insert menu and click Module.

2. Type the following code into the module sheet.



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, ".")
' 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 < ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp < "" Then Dollars = Temp & Place(Count) &
Dollars
If Len(MyNumber) 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select

Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
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("000" & MyNumber, 3)

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

' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) < "0" 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 = "" ' 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 = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between
20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
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 = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

To use this UDF push Alt+F11 and go InsertModule and paste in the
code. Push Alt+Q and save. The Function will appear under "User
Defined" in the Paste Function (Shift+F3).