I need to display numbers as words in a legal document so I need to convert a
numbers to words

See if this helps...

I altered a function I wrote:

Paste the below VBA code into a general module
€¢ Hold [Alt] and press [F11]€¦..to view the visual basic editor
€¢ Select the workbook you want to use the function in
€¢ From the main menu: <insert<module
€¢ Copy the below code and paste it into the blank module

Now use the NumsToWords() function to convert numbers to a word phrase.
Example:
A3: 800
A4: =NumsToText(A3)

Then A4 displays: Eight Hundred

'-------Start of Code---------
Option Explicit

'******************************************
'* NumsToWords() function *
'* Programmer: Ron Coderre *
'* Created on: 26-JUN-2007 *
'******************************************
Public Function NumsToWords( _
NumSource As Currency) _
As String

Dim Words As String ' Used to build the word phrase
Dim WIPnum As String ' Orig number formatted as 000000000000000.00
Dim LU_NumList() ' Array of numbers to match during the process
Dim LU_NumText() ' Text values associated with LU_NumList values
Dim iMisc As Integer ' Container for interim calculations
Dim iCtr As Integer ' Counter variable
Dim LU_Denom() ' Array of groups (Trillion, Billion, etc)

LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, _
20, 30, 40, 50, 60, 70, 80, 90)

LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
" Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
" Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
" Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
" Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")

LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")

WIPnum = Replace(Format(NumSource, "000000000000000.00;KillFlow"), ".", "0")

'Pull successive WIPnum triads and assign word values
For iCtr = 0 To 5
iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))

If Int(iMisc / 100) 0 Then
Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"
End If

'Set the tens and ones phrase
If (iMisc Mod 100) 19 Then
Words = Words _
& LU_NumText(Int((iMisc Mod 100) / 10) + 18) _
& LU_NumText(iMisc Mod 10)
Else
Words = Words & LU_NumText(iMisc Mod 100)
End If

If iMisc 0 Then Words = Words & LU_Denom(iCtr)

If iCtr = 4 Then ' Done with whole nums
Words = Words & " " & MajorCurrency
If Int(NumSource) = 0 Then Words = "None"
End If
Next iCtr

NumsToWords = Trim(Words)
End Function
'-------End of Code---------


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"bkbooker" wrote:

I need to display numbers as words in a legal document so I need to convert a
numbers to words

I tried it but wasn't succesful.
I get an error message #value in the cell.
I copied your code into a module.
(I first used NumsToText, but then realized in the code it is NumsToWords)
After closing out of VBA, I entered the formula =NumsToWords(I4) using my
cell reference.
Once entered, it takes me to VBA, which indicates I have a compile error,
variable not defined; "Major Currency" near the end of the code is highlighted

Thanks for the help


"Ron Coderre" wrote:

See if this helps...

I altered a function I wrote:

Paste the below VBA code into a general module
€¢ Hold [Alt] and press [F11]€¦..to view the visual basic editor
€¢ Select the workbook you want to use the function in
€¢ From the main menu: <insert<module
€¢ Copy the below code and paste it into the blank module

Now use the NumsToWords() function to convert numbers to a word phrase.
Example:
A3: 800
A4: =NumsToText(A3)

Then A4 displays: Eight Hundred

'-------Start of Code---------
Option Explicit

'******************************************
'* NumsToWords() function *
'* Programmer: Ron Coderre *
'* Created on: 26-JUN-2007 *
'******************************************
Public Function NumsToWords( _
NumSource As Currency) _
As String

Dim Words As String ' Used to build the word phrase
Dim WIPnum As String ' Orig number formatted as 000000000000000.00
Dim LU_NumList() ' Array of numbers to match during the process
Dim LU_NumText() ' Text values associated with LU_NumList values
Dim iMisc As Integer ' Container for interim calculations
Dim iCtr As Integer ' Counter variable
Dim LU_Denom() ' Array of groups (Trillion, Billion, etc)

LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, _
20, 30, 40, 50, 60, 70, 80, 90)

LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
" Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
" Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
" Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
" Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")

LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")

WIPnum = Replace(Format(NumSource, "000000000000000.00;KillFlow"), ".", "0")

'Pull successive WIPnum triads and assign word values
For iCtr = 0 To 5
iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))

If Int(iMisc / 100) 0 Then
Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"
End If

'Set the tens and ones phrase
If (iMisc Mod 100) 19 Then
Words = Words _
& LU_NumText(Int((iMisc Mod 100) / 10) + 18) _
& LU_NumText(iMisc Mod 10)
Else
Words = Words & LU_NumText(iMisc Mod 100)
End If

If iMisc 0 Then Words = Words & LU_Denom(iCtr)

If iCtr = 4 Then ' Done with whole nums
Words = Words & " " & MajorCurrency
If Int(NumSource) = 0 Then Words = "None"
End If
Next iCtr

NumsToWords = Trim(Words)
End Function
'-------End of Code---------


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"bkbooker" wrote:

I need to display numbers as words in a legal document so I need to convert a
numbers to words

Awww...now, see! That's what I get for hurrying.

This works:

'-------Start of Code---------
Option Explicit

'******************************************
'* NumsToWords() function *
'* Programmer: Ron Coderre *
'* Created on: 26-JUN-2007 *
'******************************************
Public Function NumsToWords( _
NumSource As Currency) _
As String

Dim Words As String ' Used to build the word phrase
Dim WIPnum As String ' Orig number formatted as 000000000000000.00
Dim LU_NumList() ' Array of numbers to match during the process
Dim LU_NumText() ' Text values associated with LU_NumList values
Dim iMisc As Integer ' Container for interim calculations
Dim iCtr As Integer ' Counter variable
Dim LU_Denom() ' Array of groups (Trillion, Billion, etc)

LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, _
20, 30, 40, 50, 60, 70, 80, 90)

LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
" Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
" Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
" Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
" Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")

LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")

WIPnum = Replace(Format(NumSource, "000000000000000.00;KillFlow"), ".", "0")

'Pull successive WIPnum triads and assign word values
For iCtr = 0 To 5
iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))

If Int(iMisc / 100) 0 Then
Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"
End If

'Set the tens and ones phrase
If (iMisc Mod 100) 19 Then
Words = Words _
& LU_NumText(Int((iMisc Mod 100) / 10) + 18) _
& LU_NumText(iMisc Mod 10)
Else
Words = Words & LU_NumText(iMisc Mod 100)
End If

If iMisc 0 Then Words = Words & LU_Denom(iCtr)

If iCtr = 4 Then ' Done with whole nums
Words = Words
If Int(NumSource) = 0 Then Words = "Zero"
End If
Next iCtr

NumsToWords = Trim(Words)
End Function
'-------End of Code---------

Does that help?

BTW....What exactly do you want to do with the number text? There may be a
better or more customized approach to take.

***********
Regards,
Ron

XL2002, WinXP


"bkbooker" wrote:

I tried it but wasn't succesful.
I get an error message #value in the cell.
I copied your code into a module.
(I first used NumsToText, but then realized in the code it is NumsToWords)
After closing out of VBA, I entered the formula =NumsToWords(I4) using my
cell reference.
Once entered, it takes me to VBA, which indicates I have a compile error,
variable not defined; "Major Currency" near the end of the code is highlighted

Thanks for the help


"Ron Coderre" wrote:

See if this helps...

I altered a function I wrote:

Paste the below VBA code into a general module
€¢ Hold [Alt] and press [F11]€¦..to view the visual basic editor
€¢ Select the workbook you want to use the function in
€¢ From the main menu: <insert<module
€¢ Copy the below code and paste it into the blank module

Now use the NumsToWords() function to convert numbers to a word phrase.
Example:
A3: 800
A4: =NumsToText(A3)

Then A4 displays: Eight Hundred

'-------Start of Code---------
Option Explicit

'******************************************
'* NumsToWords() function *
'* Programmer: Ron Coderre *
'* Created on: 26-JUN-2007 *
'******************************************
Public Function NumsToWords( _
NumSource As Currency) _
As String

Dim Words As String ' Used to build the word phrase
Dim WIPnum As String ' Orig number formatted as 000000000000000.00
Dim LU_NumList() ' Array of numbers to match during the process
Dim LU_NumText() ' Text values associated with LU_NumList values
Dim iMisc As Integer ' Container for interim calculations
Dim iCtr As Integer ' Counter variable
Dim LU_Denom() ' Array of groups (Trillion, Billion, etc)

LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, _
20, 30, 40, 50, 60, 70, 80, 90)

LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
" Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
" Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
" Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
" Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")

LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")

WIPnum = Replace(Format(NumSource, "000000000000000.00;KillFlow"), ".", "0")

'Pull successive WIPnum triads and assign word values
For iCtr = 0 To 5
iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))

If Int(iMisc / 100) 0 Then
Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"
End If

'Set the tens and ones phrase
If (iMisc Mod 100) 19 Then
Words = Words _
& LU_NumText(Int((iMisc Mod 100) / 10) + 18) _
& LU_NumText(iMisc Mod 10)
Else
Words = Words & LU_NumText(iMisc Mod 100)
End If

If iMisc 0 Then Words = Words & LU_Denom(iCtr)

If iCtr = 4 Then ' Done with whole nums
Words = Words & " " & MajorCurrency
If Int(NumSource) = 0 Then Words = "None"
End If
Next iCtr

NumsToWords = Trim(Words)
End Function
'-------End of Code---------


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"bkbooker" wrote:

I need to display numbers as words in a legal document so I need to convert a
numbers to words