# How to convert numbers to words in Excel with Hyphen - Minus Values

#### ipi99

##### New Member
Hello Everyone

Good day,

I'm using below VBA code for last 1 years and today I discovered the small error in VBA code .

When I write minus -10,000 it shows on "Only Hundred Ten Thousand"
When I write positive 10,000 it shows on "Only Ten Thousand" works fine

Can someone please correct my VBA code its not working properly when values in minus - with hyphen, your great help will be highly appreciated

Below example:

(10,000.99) Only Hundred Ten Thousand & 99/100 Riyals
10,000.99 Only Ten Thousand & 99/100 Riyals
(10,000,000.99) Only Hundred Ten Million & 99/100 Riyals
10,000,000.99 Only Ten Million & 99/100 Riyals
(10,000,000,000.99) Only Hundred Ten Billion & 99/100 Riyals
10,000,000,000.99 Only Ten Billion & 99/100 Riyals
(10,000,000,000,000.90) Only Hundred Ten Trillion & 90/100 Riyals
10,000,000,000,000.90 Only Ten Trillion & 90/100 Riyals

Formula =spellbilling

MY VBA CODE:

VBA Code:
``````'Main Function
Function SpellBilling(ByVal MyNumber)
Dim Riyals, Halalas, 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 Halalas and set MyNumber to Riyal amount.
If DecimalPlace > 0 Then
Halalas = 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 Riyals = Temp & Place(Count) & Riyals
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Riyals
Case ""
Riyals = "No Riyal"
Case "One"
Riyals = "Only One Riyal"
Case Else
Riyals = "Only " & Riyals
Riyals = Riyals & ""
End Select
Select Case Halalas
Case ""
Halalas = " & 00/00 Riyals"
Case "One"
Halalas = " & 01/100 Riyals"
Case "Two"
Halalas = " & 02/100 Riyals"
Case "Three"
Halalas = " & 03/100 Riyals"
Case "Four"
Halalas = " & 04/100 Riyals"
Case "Five"
Halalas = " & 05/100 Riyals"
Case "Six"
Halalas = " & 06/100 Riyals"
Case "Seven"
Halalas = " & 07/100 Riyals"
Case "Eight"
Halalas = " & 08/100 Riyals"
Case "Nine"
Halalas = " & 09/100 Riyals"
Case "Ten"
Halalas = " & 10/100 Riyals"
Case "Eleven"
Halalas = " & 11/100 Riyals"
Case "Twelve"
Halalas = " & 12/100 Riyals"
Case "Thirteen"
Halalas = " & 13/100 Riyals"
Case "Fourteen"
Halalas = " & 14/100 Riyals"
Case "Fifteen"
Halalas = " & 15/100 Riyals"
Case "Sixteen"
Halalas = " & 16/100 Riyals"
Case "Seventeen"
Halalas = " & 17/100 Riyals"
Case "Eighteen"
Halalas = " & 18/100 Riyals"
Case "Nineteen"
Halalas = " & 19/100 Riyals"
Case "Twenty "
Halalas = " & 20/100 Riyals"
Case "Twenty One"
Halalas = " & 21/100 Riyals"
Case "Twenty Two"
Halalas = " & 22/100 Riyals"
Case "Twenty Three"
Halalas = " & 23/100 Riyals"
Case "Twenty Four"
Halalas = " & 24/100 Riyals"
Case "Twenty Five"
Halalas = " & 25/100 Riyals"
Case "Twenty Six"
Halalas = " & 26/100 Riyals"
Case "Twenty Seven"
Halalas = " & 27/100 Riyals"
Case "Twenty Eight"
Halalas = " & 28/100 Riyals"
Case "Twenty Nine"
Halalas = " & 29/100 Riyals"
Case "Thirty "
Halalas = " & 30/100 Riyals"
Case "Thirty One"
Halalas = " & 31/100 Riyals"
Case "Thirty Two"
Halalas = " & 32/100 Riyals"
Case "Thirty Three"
Halalas = " & 33/100 Riyals"
Case "Thirty Four"
Halalas = " & 34/100 Riyals"
Case "Thirty Five"
Halalas = " & 35/100 Riyals"
Case "Thirty Six"
Halalas = " & 36/100 Riyals"
Case "Thirty Seven"
Halalas = " & 37/100 Riyals"
Case "Thirty Eight"
Halalas = " & 38/100 Riyals"
Case "Thirty Nine"
Halalas = " & 39/100 Riyals"
Case "Forty "
Halalas = " & 40/100 Riyals"
Case "Forty One"
Halalas = " & 41/100 Riyals"
Case "Forty Two"
Halalas = " & 42/100 Riyals"
Case "Forty Three"
Halalas = " & 43/100 Riyals"
Case "Forty Four"
Halalas = " & 44/100 Riyals"
Case "Forty Five"
Halalas = " & 45/100 Riyals"
Case "Forty Six"
Halalas = " & 46/100 Riyals"
Case "Forty Seven"
Halalas = " & 47/100 Riyals"
Case "Forty Eight"
Halalas = " & 48/100 Riyals"
Case "Forty Nine"
Halalas = " & 49/100 Riyals"
Case "Fifty "
Halalas = " & 50/100 Riyals"
Case "Fifty One"
Halalas = " & 51/100 Riyals"
Case "Fifty Two"
Halalas = " & 52/100 Riyals"
Case "Fifty Three"
Halalas = " & 53/100 Riyals"
Case "Fifty Four"
Halalas = " & 54/100 Riyals"
Case "Fifty Five"
Halalas = " & 55/100 Riyals"
Case "Fifty Six"
Halalas = " & 56/100 Riyals"
Case "Fifty Seven"
Halalas = " & 57/100 Riyals"
Case "Fifty Eight"
Halalas = " & 58/100 Riyals"
Case "Fifty Nine"
Halalas = " & 59/100 Riyals"
Case "Sixty "
Halalas = " & 60/100 Riyals"
Case "Sixty One"
Halalas = " & 61/100 Riyals"
Case "Sixty Two"
Halalas = " & 62/100 Riyals"
Case "Sixty Three"
Halalas = " & 63/100 Riyals"
Case "Sixty Four"
Halalas = " & 64/100 Riyals"
Case "Sixty Five"
Halalas = " & 65/100 Riyals"
Case "Sixty Six"
Halalas = " & 66/100 Riyals"
Case "Sixty Seven"
Halalas = " & 67/100 Riyals"
Case "Sixty Eight"
Halalas = " & 68/100 Riyals"
Case "Sixty Nine"
Halalas = " & 69/100 Riyals"
Case "Seventy "
Halalas = " & 70/100 Riyals"
Case "Seventy One"
Halalas = " & 71/100 Riyals"
Case "Seventy Two"
Halalas = " & 72/100 Riyals"
Case "Seventy Three"
Halalas = " & 73/100 Riyals"
Case "Seventy Four"
Halalas = " & 74/100 Riyals"
Case "Seventy Five"
Halalas = " & 75/100 Riyals"
Case "Seventy Six"
Halalas = " & 76/100 Riyals"
Case "Seventy Seven"
Halalas = " & 77/100 Riyals"
Case "Seventy Eight"
Halalas = " & 78/100 Riyals"
Case "Seventy Nine"
Halalas = " & 79/100 Riyals"
Case "Eighty "
Halalas = " & 80/100 Riyals"
Case "Eighty One"
Halalas = " & 81/100 Riyals"
Case "Eighty Two"
Halalas = " & 82/100 Riyals"
Case "Eighty Three"
Halalas = " & 83/100 Riyals"
Case "Eighty Four"
Halalas = " & 84/100 Riyals"
Case "Eighty Five"
Halalas = " & 85/100 Riyals"
Case "Eighty Six"
Halalas = " & 86/100 Riyals"
Case "Eighty Seven"
Halalas = " & 87/100 Riyals"
Case "Eighty Eight"
Halalas = " & 88/100 Riyals"
Case "Eighty Nine"
Halalas = " & 89/100 Riyals"
Case "Ninety "
Halalas = " & 90/100 Riyals"
Case "Ninety One"
Halalas = " & 91/100 Riyals"
Case "Ninety Two"
Halalas = " & 92/100 Riyals"
Case "Ninety Three"
Halalas = " & 93/100 Riyals"
Case "Ninety Four"
Halalas = " & 94/100 Riyals"
Case "Ninety Five"
Halalas = " & 95/100 Riyals"
Case "Ninety Six"
Halalas = " & 96/100 Riyals"
Case "Ninety Seven"
Halalas = " & 97/100 Riyals"
Case "Ninety Eight"
Halalas = " & 98/100 Riyals"
Case "Ninety Nine"
Halalas = " & 99/100 Riyals"

Case Else
Halalas = " & " & Halalas & " Halalas"
End Select
SpellBilling = Riyals & Halalas
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``````

#### ipi99

##### New Member
OMG! It works great Thank you for your great support I really appreciate all your time and efforts.

There is small thing find out when I write -1 it doesn't show "minus" in the beginning as you can see in the attached photo

 -1 Only One Riyal & 00/00 Riyals

Updated code:

VBA Code:
``````'Main Function
Function SpellBilling(ByVal MyNumber)
Dim Riyals, Halalas, Temp
Dim DecimalPlace, Count
Dim bNeg As Boolean
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))
bNeg = Left(MyNumber, 1) = "-"
MyNumber = Replace(MyNumber, "-", "")
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Halalas and set MyNumber to Riyal amount.
If DecimalPlace > 0 Then
Halalas = 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 Riyals = Temp & Place(Count) & Riyals
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Riyals
Case ""
Riyals = "No Riyal"
Case "One"
Riyals = "Only One Riyal"
Case Else
Riyals = "Only " & Riyals
Riyals = Riyals & ""
If bNeg Then Riyals = Replace(Riyals, "Only", "Only Minus")
End Select
Select Case Halalas
Case ""
Halalas = " & 00/00 Riyals"
Case "One"
Halalas = " & 01/100 Riyals"
Case "Two"
Halalas = " & 02/100 Riyals"
Case "Three"
Halalas = " & 03/100 Riyals"
Case "Four"
Halalas = " & 04/100 Riyals"
Case "Five"
Halalas = " & 05/100 Riyals"
Case "Six"
Halalas = " & 06/100 Riyals"
Case "Seven"
Halalas = " & 07/100 Riyals"
Case "Eight"
Halalas = " & 08/100 Riyals"
Case "Nine"
Halalas = " & 09/100 Riyals"
Case "Ten"
Halalas = " & 10/100 Riyals"
Case "Eleven"
Halalas = " & 11/100 Riyals"
Case "Twelve"
Halalas = " & 12/100 Riyals"
Case "Thirteen"
Halalas = " & 13/100 Riyals"
Case "Fourteen"
Halalas = " & 14/100 Riyals"
Case "Fifteen"
Halalas = " & 15/100 Riyals"
Case "Sixteen"
Halalas = " & 16/100 Riyals"
Case "Seventeen"
Halalas = " & 17/100 Riyals"
Case "Eighteen"
Halalas = " & 18/100 Riyals"
Case "Nineteen"
Halalas = " & 19/100 Riyals"
Case "Twenty "
Halalas = " & 20/100 Riyals"
Case "Twenty One"
Halalas = " & 21/100 Riyals"
Case "Twenty Two"
Halalas = " & 22/100 Riyals"
Case "Twenty Three"
Halalas = " & 23/100 Riyals"
Case "Twenty Four"
Halalas = " & 24/100 Riyals"
Case "Twenty Five"
Halalas = " & 25/100 Riyals"
Case "Twenty Six"
Halalas = " & 26/100 Riyals"
Case "Twenty Seven"
Halalas = " & 27/100 Riyals"
Case "Twenty Eight"
Halalas = " & 28/100 Riyals"
Case "Twenty Nine"
Halalas = " & 29/100 Riyals"
Case "Thirty "
Halalas = " & 30/100 Riyals"
Case "Thirty One"
Halalas = " & 31/100 Riyals"
Case "Thirty Two"
Halalas = " & 32/100 Riyals"
Case "Thirty Three"
Halalas = " & 33/100 Riyals"
Case "Thirty Four"
Halalas = " & 34/100 Riyals"
Case "Thirty Five"
Halalas = " & 35/100 Riyals"
Case "Thirty Six"
Halalas = " & 36/100 Riyals"
Case "Thirty Seven"
Halalas = " & 37/100 Riyals"
Case "Thirty Eight"
Halalas = " & 38/100 Riyals"
Case "Thirty Nine"
Halalas = " & 39/100 Riyals"
Case "Forty "
Halalas = " & 40/100 Riyals"
Case "Forty One"
Halalas = " & 41/100 Riyals"
Case "Forty Two"
Halalas = " & 42/100 Riyals"
Case "Forty Three"
Halalas = " & 43/100 Riyals"
Case "Forty Four"
Halalas = " & 44/100 Riyals"
Case "Forty Five"
Halalas = " & 45/100 Riyals"
Case "Forty Six"
Halalas = " & 46/100 Riyals"
Case "Forty Seven"
Halalas = " & 47/100 Riyals"
Case "Forty Eight"
Halalas = " & 48/100 Riyals"
Case "Forty Nine"
Halalas = " & 49/100 Riyals"
Case "Fifty "
Halalas = " & 50/100 Riyals"
Case "Fifty One"
Halalas = " & 51/100 Riyals"
Case "Fifty Two"
Halalas = " & 52/100 Riyals"
Case "Fifty Three"
Halalas = " & 53/100 Riyals"
Case "Fifty Four"
Halalas = " & 54/100 Riyals"
Case "Fifty Five"
Halalas = " & 55/100 Riyals"
Case "Fifty Six"
Halalas = " & 56/100 Riyals"
Case "Fifty Seven"
Halalas = " & 57/100 Riyals"
Case "Fifty Eight"
Halalas = " & 58/100 Riyals"
Case "Fifty Nine"
Halalas = " & 59/100 Riyals"
Case "Sixty "
Halalas = " & 60/100 Riyals"
Case "Sixty One"
Halalas = " & 61/100 Riyals"
Case "Sixty Two"
Halalas = " & 62/100 Riyals"
Case "Sixty Three"
Halalas = " & 63/100 Riyals"
Case "Sixty Four"
Halalas = " & 64/100 Riyals"
Case "Sixty Five"
Halalas = " & 65/100 Riyals"
Case "Sixty Six"
Halalas = " & 66/100 Riyals"
Case "Sixty Seven"
Halalas = " & 67/100 Riyals"
Case "Sixty Eight"
Halalas = " & 68/100 Riyals"
Case "Sixty Nine"
Halalas = " & 69/100 Riyals"
Case "Seventy "
Halalas = " & 70/100 Riyals"
Case "Seventy One"
Halalas = " & 71/100 Riyals"
Case "Seventy Two"
Halalas = " & 72/100 Riyals"
Case "Seventy Three"
Halalas = " & 73/100 Riyals"
Case "Seventy Four"
Halalas = " & 74/100 Riyals"
Case "Seventy Five"
Halalas = " & 75/100 Riyals"
Case "Seventy Six"
Halalas = " & 76/100 Riyals"
Case "Seventy Seven"
Halalas = " & 77/100 Riyals"
Case "Seventy Eight"
Halalas = " & 78/100 Riyals"
Case "Seventy Nine"
Halalas = " & 79/100 Riyals"
Case "Eighty "
Halalas = " & 80/100 Riyals"
Case "Eighty One"
Halalas = " & 81/100 Riyals"
Case "Eighty Two"
Halalas = " & 82/100 Riyals"
Case "Eighty Three"
Halalas = " & 83/100 Riyals"
Case "Eighty Four"
Halalas = " & 84/100 Riyals"
Case "Eighty Five"
Halalas = " & 85/100 Riyals"
Case "Eighty Six"
Halalas = " & 86/100 Riyals"
Case "Eighty Seven"
Halalas = " & 87/100 Riyals"
Case "Eighty Eight"
Halalas = " & 88/100 Riyals"
Case "Eighty Nine"
Halalas = " & 89/100 Riyals"
Case "Ninety "
Halalas = " & 90/100 Riyals"
Case "Ninety One"
Halalas = " & 91/100 Riyals"
Case "Ninety Two"
Halalas = " & 92/100 Riyals"
Case "Ninety Three"
Halalas = " & 93/100 Riyals"
Case "Ninety Four"
Halalas = " & 94/100 Riyals"
Case "Ninety Five"
Halalas = " & 95/100 Riyals"
Case "Ninety Six"
Halalas = " & 96/100 Riyals"
Case "Ninety Seven"
Halalas = " & 97/100 Riyals"
Case "Ninety Eight"
Halalas = " & 98/100 Riyals"
Case "Ninety Nine"
Halalas = " & 99/100 Riyals"

Case Else
Halalas = " & " & Halalas & " Halalas"
End Select
SpellBilling = Riyals & Halalas
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``````

#### Attachments

• 33.PNG
112.2 KB · Views: 1
Last edited:

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Peter_SSs

##### MrExcel MVP, Moderator
Oops, one line in the wrong spot. Try moving this line..

Rich (BB code):
``````If bNeg Then Riyals = Replace(Riyals, "Only", "Only Minus")
End Select
If bNeg Then Riyals = Replace(Riyals, "Only", "Only Minus")``````

Replies
2
Views
462
Replies
1
Views
515
Replies
15
Views
399
Replies
1
Views
293
Replies
4
Views
143

1,128,126
Messages
5,628,858
Members
416,345
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back