With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

I'm required to create a report that prints a number in 'thousands', 'hundreds', 'tens', 'units'. For example $104 must be written as:

Thousands Hundreds Tens Units

Zero One Zero Four

I'm having a problem with leading zero(s) in this report. Any ideas how to script this function in the modules?

Thousands Hundreds Tens Units

Zero One Zero Four

I'm having a problem with leading zero(s) in this report. Any ideas how to script this function in the modules?

However, I already have this case structure in the module function. What I need to know is how to pad the leading zeroes.

As another example $1 must have leading 'zeros' in the thousands, hundreds and tens, so:

Thousands Hundreds Tens Units

Zero Zero Zero One

The function you have given does not do this it will give blanks instead of the leading zeros as below:

Thousands Hundreds Tens Units

One

? getWords(1000, 104)

Then, as stated you'd call the function for each position.

Although I'd suggest a change to the function to use this as the Select Case statement

Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)

Option Compare Database

Option Explicit

Public Function ConvertCurrencyToWords(ByV

Dim Temp

Dim Dollars, Cents

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " " 'Was " Thousand "

Place(3) = " " 'Was " Million "

Place(4) = " " 'Was " Billion "

Place(5) = " " 'Was " Trillion "

' Convert MyNumber to a string, trimming extra spaces.

MyNumber = Trim(Str(MyNumber))

' Find decimal place.

DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...

If DecimalPlace > 0 Then

' Convert cents

Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

Cents = ConvertTens(Temp)

' Strip off cents from remainder to convert.

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

' Convert last 3 digits of MyNumber to English dollars.

Temp = ConvertHundreds(Right(MyNu

If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

' Remove last 3 converted digits from MyNumber.

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

' Clean up dollars.

Select Case Dollars

Case "" 'ADD 10 SPACES TO Dollars FIELD

Dollars = "Zero Zero Zero Zero " 'was "No Dollars"

Case "One"

Dollars = "One " 'was "One Dollar"

Case Else

Dollars = Dollars & " " 'NOT IN USED

End Select

' Clean up cents.

'Select Case Cents

' Case ""

' Cents = " And No Cents"

' Case "One"

' Cents = " And One Cent"

' Case Else

' Cents = " And " & Cents & " Cents"

'End Select

ConvertCurrencyToWords = Dollars '& Cents - Removes the cents from the calc

End Function

Private Function ConvertHundreds(ByVal MyNumber)

Dim Result As String

' Exit if there is nothing to convert.

If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.

MyNumber = Right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?

If Left(MyNumber, 1) <> "0" Then

Result = ConvertDigit(Left(MyNumber

End If

' Do we have a tens place digit to convert?

If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & ConvertTens(Mid(MyNumber, 2))

Else

' If not, then convert the ones place digit.

Result = Result & ConvertDigit(Mid(MyNumber,

End If

'ConvertHundreds = Trim(Result) - REMOVE TRIM EXTRA SPACES

ConvertHundreds = Result

End Function

Private Function ConvertTens(ByVal MyTens)

Dim Result As String

' Is value between 10 and 19?

If Val(Left(MyTens, 1)) = 1 Then

Select Case Val(MyTens)

Case 10: Result = "One Zero"

Case 11: Result = "One One"

Case 12: Result = "One Two"

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

' .. otherwise it's between 20 and 99.

Select Case Val(Left(MyTens, 1)) '-ADD SPACES AFTER Result FIELD

Case 2: Result = "Two "

Case 3: Result = "Three "

Case 4: Result = "Four "

Case 5: Result = "Five "

Case 6: Result = "Six "

Case 7: Result = "Seven "

Case 8: Result = "Eight "

Case 9: Result = "Nine "

Case Else

End Select

' Convert ones place digit.

Result = Result & ConvertDigit(Right(MyTens,

End If

ConvertTens = Result

End Function

Private Function ConvertDigit(ByVal MyDigit)

Select Case Val(MyDigit) ' - ADD SPACES

Case 0: ConvertDigit = "Zero "

Case 1: ConvertDigit = "One "

Case 2: ConvertDigit = "Two "

Case 3: ConvertDigit = "Three "

Case 4: ConvertDigit = "Four "

Case 5: ConvertDigit = "Five "

Case 6: ConvertDigit = "Six "

Case 7: ConvertDigit = "Seven "

Case 8: ConvertDigit = "Eight "

Case 9: ConvertDigit = "Nine "

Case Else: ConvertDigit = " " 'NOT IN USED

End Select

End Function

Yes. I need the 'numbers in words' to match up with the relating unit columns as illustrated in the example in the question.

'Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)'

I'm not sure how to fit this in with the syntax I pasted above.

So you'd just call

ConvertCurrencyToWords(201

and get

Thousands Hundreds Tens Units

Zero Two Zero One

And are you using a fixed spaced font, like Courier, to display the results?

(Assuming so - with your apparent use of spacing to pad it out)

So as you can see I've hard coded $0 to show:

Thousands Hundreds Tens Units

Zero Zero Zero Zero

However, currently $1 is appearing as:

Thousands Hundreds Tens Units

one

which is without the leading zeroes. This is happening for all leading zeroes, they simply aren't showing us.

I'm not sure where in my script things are going wrong, any ideas?

Yes, I'm using a fix spaced font - though if you have a better solution I'm open to using something else rather than using spaces to pad out.

Do you not *want* to be able to pass an amount (less than 10,000) to a function and recieve your total back?

And the alternative is a function which considers only each placement of a character (much like suggested by Raynard7 originally).

So you'd have a column structure set up with the headings for Thousands Hundreds Tens Units and a control under each.

In the control you'd have

Thousands Hundreds Tens Units

getWords(4,[FieldName]) getWords(3,[FieldName]) getWords(2,[FieldName]) getWords(1,[FieldName])

That way you can use whatever font you wish.

You'd just have to make that subtle ammendment to that function which I suggested. :-)

I'm still having a problem though. I'll try and explain:

I have a series of numbers that differs on each page of a report that I have written. Each number must be printed in words and numberals. So lets say that I have a series of numbers known as 'xNumbers' appearing on different pages of my report:

25324

1008

19500

13520

In my report I have a single 'text block' with a 'control source'. The 'control source' is my module. My module's name is 'test'. So, in the text box I have written "=test([xNumbers])"

Above this test box I currently have the words:

Thousands Hundreds Tens Units

So my current script (as posted) returns the numbers in words (upto 19000) in this text box. Anything over 19000 doesn't really work quite right (which I also need to fix), and if the number is in the 100's or tens then the leading zeroes do not print.

I've tested Raynard7's script and I can't get it working with my report as I need it to be able to use the 'xNumber's in the reports.

So, do I need to create a module for each of the units to be written? If so how do I get them into the column structure? Would I do this with say five or six text boxes under a sturcture like:

Hundreds Thousands Ten Thousands Thousands Hundreds Tens Units

Text box Text box Text box Text box Text box Text box

Each with a differnet function "=test1s([xNumbers])", "=test10s([xNumbers])", "=test100s([xNumbers])". etc.

What does my script need to look like in order to use the xNumbers I have?

My boss will be back next week so I'll be a bit clearer then. Until then have a good weekend and thanks for you help thus far.

So xNumbers is a field from which you want to break out the relavent information yes?

I strongly urge you to drop your own function.

No offence - but it's inefficent and unnecessary (in that there's lots of somewhat repetative code).

The simple function outlined (with the one modification) should be enough.

Hundreds Thousands Ten Thousands Thousands Hundreds Tens Units

=getWords(6,[xNumbers]) =getWords(5,[xNumbers]) =getWords(4,[xNumbers]) =getWords(3,[xNumbers]) =getWords(2,[xNumbers]) =getWords(1,[xNumbers])

Does that not do what you require?

Thanks.

Have you been implementing the changes and suggestions as we've gone through here - or just continuously fiddling with it yourself until it suddenly worked for you - all by itself?

Thank you both for your help. I'll be splinting the points.

I've posted the script below for other people's reference:

Public Function getWords(columnNo As Long, value As Long) As String

Select Case Right(CStr(value \ columnNo), 1)

Case 0

getWords = "zero "

Case 1

getWords = "one "

Case 2

getWords = "two "

Case 3

getWords = "three"

Case 4

getWords = "four "

Case 5

getWords = "five "

Case 6

getWords = "six "

Case 7

getWords = "seven"

Case 8

getWords = "eight"

Case 9

getWords = "nine "

End Select

End Function

Public Function NumberToConvert(MyNumber As Long)

Dim txtUni, txtTen, txtHun, txtTho, txtTenTho, txtHunTho

Dim NumberLength As Integer

NumberLength = Len(CStr(MyNumber))

'Always Reported

txtUni = getWords(1, MyNumber)

txtTen = getWords(10, MyNumber) & " "

txtHun = getWords(100, MyNumber) & " "

txtTho = getWords(1000, MyNumber) & " "

'Only reported if Number of greater length

If NumberLength > 4 Then

txtTenTho = getWords(10000, MyNumber) & " "

End If

If NumberLength > 5 Then

txtHunTho = getWords(100000, MyNumber) & " "

End If

NumberToConvert = txtHunTho & txtTenTho & txtTho & txtHun & txtTen & txtUni

End Function

LPurvis thank you very much for your help. Sorry that I didn't know how to spit the points.

i.e. using

Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)

But you know in the end, after all that, we don't need to change the numbers into words due to a legislative change by the government. So thanks for your help and thanks to the government who made it more a learning exercise and of absolute importance.

All Courses

From novice to tech pro — start learning today.

where you pass in the colunn ie (1000) or (100) ...and the value of the number $104

Public Function getWords(columnNo As Long, value As Long) As String

Select Case Right(CStr(value \ columnNo), 1)

Case 0

getWords = "zero"

Case 1

getWords = "one"

Case 2

getWords = "two"

Case 3

getWords = "three"

Case 4

getWords = "four"

Case 5

getWords = "five"

Case 6

getWords = "six"

Case 7

getWords = "seven"

Case 8

getWords = "eight"

Case 9

getWords = "nine"

End Select

End Function