G
Guest
I need to display numbers as words in a legal document so I need to convert a
numbers to words
numbers to words
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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
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
G
Guest
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
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 said: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 said:I need to display numbers as words in a legal document so I need to convert a
numbers to words
G
Guest
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
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 said: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 said: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 said:I need to display numbers as words in a legal document so I need to convert a
numbers to words
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.