Hi, I need to change the NumberToWord Macro to write e.g. "One Hundred Seven and Twenty" (for my language) for 127 and NOT "One Hundred Twenty Seven" like in English.
Please advise
Thank you.
Hi, I need to change the NumberToWord Macro to write e.g. "One Hundred Seven and Twenty" (for my language) for 127 and NOT "One Hundred Twenty Seven" like in English.
Please advise
Thank you.
I assume you mean the one available on the MS site which gives you 'one hundred and twenty three dollars and forty five cents' or similar?
If so, try this - it's my amended version with euros and cents, with Dutch numbers - probably similar to Afrikaans if that's what you need.
I haven't tested it a lot, but it seems to be okay.
![]()
Option Explicit ' From: https://support.office.com/en-us/article/Convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98 ' Nummers in het Nederlands ' Public Function SpellNumberEuroNL(ByVal MyNumber) 'Main Function Dim Euros, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " duizend " Place(3) = " miljoen " Place(4) = " miljard " Place(5) = " biljoen " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert cents and set MyNumber to euro 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 Euros = Temp & Place(Count) & Euros If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Euros Case "" Euros = "Geen euros" Case "één" Euros = "Één euro" Case Else Euros = Euros & " euros" End Select Select Case Cents Case "" Cents = " en geen cents" Case "One" Cents = " en één cent" Case Else Cents = " en " & Cents & " cents" End Select SpellNumberEuroNL = Euros & Cents End Function Function GetHundreds(ByVal MyNumber) ' Converts a number from 100-999 into text 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)) & " honderd " 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 Function GetTens(TensText) ' Converts a number from 10 to 99 into text. 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 = "tien" Case 11: Result = "elf" Case 12: Result = "twaalf" Case 13: Result = "dertien" Case 14: Result = "viertien" Case 15: Result = "vijftien" Case 16: Result = "zestien" Case 17: Result = "zeventien" Case 18: Result = "achttien" Case 19: Result = "negentien" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = " en twintig" Case 3: Result = " en dertig" Case 4: Result = " en viertig" Case 5: Result = " en vijftig" Case 6: Result = " en zestig" Case 7: Result = " en zeventig" Case 8: Result = " en tachtig" Case 9: Result = " en negentig" Case Else End Select Result = GetDigit(Right(TensText, 1)) & Result ' Retrieve ones place. End If GetTens = Result End Function Function GetDigit(Digit) ' Converts a number from 1 to 9 into text. Select Case Val(Digit) Case 1: GetDigit = "één" Case 2: GetDigit = "twee" Case 3: GetDigit = "drie" Case 4: GetDigit = "vier" Case 5: GetDigit = "vijf" Case 6: GetDigit = "zes" Case 7: GetDigit = "zeven" Case 8: GetDigit = "acht" Case 9: GetDigit = "negen" Case Else: GetDigit = "" End Select End Function
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Hi, thanx for the reply.
I've changed it but still sitting with a problem.
In my language the wording is as follows: All numbers from 001 - 019 and all x01 - x19 are written as "en" and the amount
e.g. one hundred en(and) one, ....... one hundred en(and) nineteen, ....... nine hundred en(and) nineteen.
I’ve managed to change the "one hundred en(and) twenty" up to "one hundred en(and) ninety".
I'll be glad if somebody can help me if possible.
Thanx in advance
Johan
Change the part noted below to add 'en ' and I think it then works:
The original code is written to give the written numbers the way the Americans do it (example 214 = 'two hundred fourteen'). In the UK we add 'and' just as you want ('two hundred and fourteen').![]()
' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " honderd en " End If
I think the amendment above will do what you want - I've quickly tested a few examples - but if it doesn't then please ask again. I'll then have to call in some expert VBA help - I'm just a novice with VBA.
Hallo daar Johan...
I am assuming this to be Afrikaans...Very tricky...(for my language)
Have adapted code to suite your requirements...The attached seems to work...
Am not always available as I'm busy with a project so can only follow up on your response later again...
Last edited by Sintek; 07-19-2019 at 05:24 AM.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks