How to Spell Number Without Currency in Excel (2 Suitable Ways)

We often need to spell number in Excel without currency in our day-to-day life. There is no direct and simple function in Excel to do it. We can create custom functions according to our needs in Excel, a versatile application. In this article, we will learn 2 suitable ways to spell number without currency in Excel.


Download Practice Workbook


2 Suitable Methods to Spell Number Without Currency in Excel

There is no in-built function in Excel to spell number without currency. For this reason, we will create our function here using Excel VBA. We will also use just an Excel formula to spell number without currency. Let’s say, we have some Numbers with 2 decimal points. Our goal is to spell the Numbers without currency.

How to Spell Number Without Currency in Excel

Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.


1. Using Excel Formula to Spell Number in Excel

In the first method, we will use an Excel formula to spell number without currency. The formula is quite complicated and long. It consists of more than 2700 characters! But don’t worry! You don’t need to understand it completely. You just need to know how to use it. Moreover, you can always copy the formula from our article whenever you need it.

Although the formula is pretty big, it can make your work immensely easier. Let’s follow the steps mentioned below to spell number without currency in Excel.

Steps:

  • Firstly, enter the following formula in cell B5.
=CHOOSE(LEFT(TEXT(B5,"000000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--LEFT(TEXT(B5,"000000000000.00"))=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),2,1)=0,--MID(TEXT(B5,"000000000000.00"),3,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B5,"000000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B5,"000000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(B5,"000000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--LEFT(TEXT(B5,"000000000000.00"))+MID(TEXT(B5,"000000000000.00"),2,1)+MID(TEXT(B5,"000000000000.00"),3,1))=0, ,IF(AND((--MID(TEXT(B5,"000000000000.00"),4,1)+MID(TEXT(B5,"000000000000.00"),5,1)+MID(TEXT(B5,"000000000000.00"),6,1)+MID(TEXT(B5,"000000000000.00"),7,1))=0,(--MID(TEXT(B5,"000000000000.00"),8,1)+RIGHT(TEXT(B5,"000000000000.00")))>0),"Billion and ","Billion "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(B5,"000000000000.00"),4,1)=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),5,1)=0,--MID(TEXT(B5,"000000000000.00"),6,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B5,"000000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B5,"000000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(B5,"000000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--MID(TEXT(B5,"000000000000.00"),4,1)+MID(TEXT(B5,"000000000000.00"),5,1)+MID(TEXT(B5,"000000000000.00"),6,1))=0, ,IF(OR((--MID(TEXT(B5,"000000000000.00"),7,1)+MID(TEXT(B5,"000000000000.00"),8,1)+MID(TEXT(B5,"000000000000.00"),9,1))=0,--MID(TEXT(B5,"000000000000.00"),7,1)>0),"Million ","Million and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(B5,"000000000000.00"),7,1)=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),8,1)=0,--MID(TEXT(B5,"000000000000.00"),9,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B5,"000000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B5,"000000000000.00"),9,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(B5,"000000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--MID(TEXT(B5,"000000000000.00"),7,1)+MID(TEXT(B5,"000000000000.00"),8,1)+MID(TEXT(B5,"000000000000.00"),9,1))=0, ,IF(OR((--MID(TEXT(B5,"000000000000.00"),10,1)+MID(TEXT(B5,"000000000000.00"),11,1)+MID(TEXT(B5,"000000000000.00"),12,1))=0,--MID(TEXT(B5,"000000000000.00"),10,1)<>0),"Thousand ","Thousand and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),10,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(B5,"000000000000.00"),10,1)=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),11,1)=0,--MID(TEXT(B5,"000000000000.00"),12,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(B5,"000000000000.00"),11,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B5,"000000000000.00"),11,1)<>1,CHOOSE(MID(TEXT(B5,"000000000000.00"),12,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(B5,"000000000000.00"),12,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&"Point "&CHOOSE(MID(TEXT(B5,"000000000000.00"),14,1)+2,,"zero ","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&CHOOSE(MID(TEXT(B5,"000000000000.00"),15,1)+2,,"zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

Here, cell B5 represents the cell of the column named Numbers.

  • After that, press ENTER.

Using Excel Formula to Spell Number in Excel

Before we proceed to the Formula Breakdown section, let me introduce the functions that we used here. We used 4 functions here. They are as follows.

You don’t need to dissect each part of the formula as the formula is kind of a repetitive procedure. You just need to understand the following section of the formula.

CHOOSE(LEFT(TEXT(B5,"000000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--LEFT(TEXT(B5,"000000000000.00"))=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),2,1)=0,--MID(TEXT(B5,"000000000000.00"),3,1)=0),"Hundred ","Hundred and 

Let’s divide it into 2 sections for further simplification.

CHOOSE(LEFT(TEXT(B5,"000000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--LEFT(TEXT(B5,"000000000000.00"))=0, ,IF(AND(--MID(TEXT(B5,"000000000000.00"),2,1)=0,--MID(TEXT(B5,"000000000000.00"),3,1)=0),"Hundred ","Hundred and 

Formula Breakdown

First Part:

  • TEXT(B5,”000000000000.00″) → It converts the number of cell B5 into 000000000000.00 format.
  • LEFT(TEXT(B5,”000000000000.00″)) → This returns the left-most character from the given number so that we know whether the returned number is 0 or any other value.
  • CHOOSE(LEFT(TEXT(B5,”000000000000.00″))+1,,”One “,”Two “,”Three “,”Four “,”Five “,”Six “,”Seven “,”Eight “,”Nine “) → Now, we choose which text to show based on the extracted number.

Second Part:

  • CHOOSE(LEFT(TEXT(B5,”000000000000.00″))+1,,”One “,”Two “,”Three “,”Four “,”Five “,”Six “,”Seven “,”Eight “,”Nine “)&IF(–LEFT(TEXT(B5,”000000000000.00”))=0 → Here, we are  checking if the value is 0. If it is 0, then we are not in the Billions range. So, we will not display anything.
  • CHOOSE(LEFT(TEXT(B5,”000000000000.00″))+1,,”One “,”Two “,”Three “,”Four “,”Five “,”Six “,”Seven “,”Eight “,”Nine “)&IF(–LEFT(TEXT(B5,”000000000000.00″))=0, ,IF(AND(–MID(TEXT(B5,”000000000000.00″),2,1)=0,–MID(TEXT(B5,”000000000000.00”),3,1)=0),”Hundred “,”Hundred and → Now, we will check the following 2 digits are 0 or not. If they are 0 then return Hundred otherwise return Hundred and.

I strongly believe that if you can grasp this portion of the formula, you will be able to understand the rest of the formula in no time.

Consequently, you will see that the number in cell B5 is spelled out as shown in the following image.

  • Now, use the AutoFill feature of Excel to obtain the rest of the outputs as demonstrated in the image below.


2. Utilizing Excel VBA to Spell Number

Utilizing the Excel VBA is a smart way to spell number without currency in Excel. Let’s use the procedure discussed in the following section to do this.

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • Following that, select the Visual Basic option from the Code group.

Utilizing Excel VBA to Spell Number in Excel

As a result, the Microsoft Visual Basic window will open as shown in the following picture.

  • Now, in the Microsoft Visual Basic window, go to the Insert tab.
  • Then, choose the Module option from the drop-down.

Inseting module to Spell Number Without Currency in Excel

  • After that, write the following code in the newly created module.
Option Explicit
Function spelling_number(ByVal given_number)
Dim us_dollars, us_cents, temp
Dim decimal_point, count
ReDim Position(9) As String
Position(2) = " Thousand "
Position(3) = " Million "
Position(4) = " Billion "
Position(5) = " Trillion "
given_number = Trim(Str(given_number))
decimal_point = InStr(given_number, ".")
If decimal_point > 0 Then
us_cents = GetDigit(Left(Mid(given_number, decimal_point + 1) & _
"00", 1)) & " " & GetDigit(Left(Mid(given_number, decimal_point + 2) & _
"00", 1))
given_number = Trim(Left(given_number, decimal_point - 1))
End If
count = 1
Do While given_number <> ""
temp = GetHundreds(Right(given_number, 3))
If temp <> "" Then us_dollars = temp & Position(count) & us_dollars
If Len(given_number) > 3 Then
given_number = Left(given_number, Len(given_number) - 3)
Else
given_number = ""
End If
count = count + 1
Loop
Select Case us_cents
Case ""
us_cents = ""
Case "One"
us_cents = " Point One "
Case Else
us_cents = " Point " & us_cents & " "
End Select
spelling_number = us_dollars & us_cents
End Function
Function GetHundreds(ByVal given_number)
Dim output As String
If Val(given_number) = 0 Then Exit Function
given_number = Right("000" & given_number, 3)
If Mid(given_number, 1, 1) <> "0" Then
output = GetDigit(Mid(given_number, 1, 1)) & " Hundred "
End If
If Mid(given_number, 2, 1) <> "0" Then
output = output & GetTens(Mid(given_number, 2))
Else
output = output & GetDigit(Mid(given_number, 3))
End If
GetHundreds = output
End Function
Function GetTens(tens_text)
Dim output As String
output = ""
If Val(Left(tens_text, 1)) = 1 Then
Select Case Val(tens_text)
Case 10: output = "Ten"
Case 11: output = "Eleven"
Case 12: output = "Twelve"
Case 13: output = "Thirteen"
Case 14: output = "Fourteen"
Case 15: output = "Fifteen"
Case 16: output = "Sixteen"
Case 17: output = "Seventeen"
Case 18: output = "Eighteen"
Case 19: output = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(tens_text, 1))
Case 2: output = "Twenty "
Case 3: output = "Thirty "
Case 4: output = "Forty "
Case 5: output = "Fifty "
Case 6: output = "Sixty "
Case 7: output = "Seventy "
Case 8: output = "Eighty "
Case 9: output = "Ninety "
Case Else
End Select
output = output & GetDigit _
(Right(tens_text, 1))
End If
GetTens = output
End Function
Function GetDigit(number)
Select Case Val(number)
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

Code Breakdown

  • Firstly, we defined the name of the function as well as the value of the function.
Option Explicit
Function spelling_number(ByVal given_number)
  • After that, we declared the required variable names and the string along with the string size.
Dim us_dollars, us_cents, temp
Dim decimal_point, count
ReDim Position(9) As String
Position(2) = " Thousand "
Position(3) = " Million "
Position(4) = " Billion "
Position(5) = " Trillion "
  • Next, we excluded the extra spaces from the string by using the TRIM function.
given_number = Trim(Str(given_number))
  • Following that, we used the InStr function to determine the place to set the position of the decimal point.
decimal_point = InStr(given_number, ".")
  • Now, we will check if the position of the decimal point is greater than 0 or not. If the condition satisfies, assign the value 1 to the count variable.
If decimal_point > 0 Then
us_cents = GetDigit(Left(Mid(given_number, decimal_point + 1) & _
"00", 1)) & " " & GetDigit(Left(Mid(given_number, decimal_point + 2) & _
"00", 1))
given_number = Trim(Left(given_number, decimal_point - 1))
End If
count = 1
  • After that, we used the Do While Loop and checked whether the value of the temp variable is 0 or not by an IF statement to assign the number value.
  • Then, we used another IF statement to check the length of the given_number, whether it is greater than 3 or not.
  • Next, increase the value of the variable count after each successful run of the loop.
  • Following that, we closed the loop.
Do While given_number <> ""
temp = GetHundreds(Right(given_number, 3))
If temp <> "" Then us_dollars = temp & Position(count) & us_dollars
If Len(given_number) > 3 Then
given_number = Left(given_number, Len(given_number) - 3)
Else
given_number = ""
End If
count = count + 1
Loop
  • Here, for the first case, if there is nothing after a decimal point then it will return a blank.
  • For Case One, it will return Point One.
  • Otherwise, it returns the decimal amount.
Select Case us_cents
Case ""
us_cents = ""
Case "One"
us_cents = " Point One "
Case Else
us_cents = " Point " & us_cents & " "
End Select
  • After that, we assigned the values to the spelling_number function and ended the function.
spelling_number = us_dollars & us_cents
End Function
  • Following that, we defined another function to convert a number from 100-999.
Function GetHundreds(ByVal given_number)
  • Then, we declared a variable named output as String.
Dim output As String
  • Now, we use an If statement. If the Val function returns 0 then the function will terminate.
If Val(given_number) = 0 Then Exit Function
given_number = Right("000" & given_number, 3)
  • Then, if the output of the Mid function is not equal to 0, assign the hundreds place to the output variable.
If Mid(given_number, 1, 1) <> "0" Then
output = GetDigit(Mid(given_number, 1, 1)) & " Hundred "
End If
  • Here we used the Mid function which initiates from the 2nd character and ends after 1 character.
  • Following that, we checked if the value is not equal to 0 or not.
  • If it is not equal to 0 then we used the GetTens to convert the tens place.
  • Otherwise, it will take the output by converting the one’s place using the GetDigit.
If Mid(given_number, 1, 1) <> "0" Then
output = GetDigit(Mid(given_number, 1, 1)) & " Hundred "
End If
  • Subsequently, we assigned the output value to the GetHundreds and ended the function.
GetHundreds = output
End Function
  • Then, we declared a variable named output as String.
Dim output As String
  • After that, we  declared the output as null
output = ""
  • Now, we used an If statement to check if the value returned by the Val function and the associated functions in it is equal to 1 or not. If it is 1, then it returns the defined cases as output.
  • Subsequently, terminate the process.
If Val(Left(tens_text, 1)) = 1 Then
Select Case Val(tens_text)
Case 10: output = "Ten"
Case 11: output = "Eleven"
Case 12: output = "Twelve"
Case 13: output = "Thirteen"
Case 14: output = "Fourteen"
Case 15: output = "Fifteen"
Case 16: output = "Sixteen"
Case 17: output = "Seventeen"
Case 18: output = "Eighteen"
Case 19: output = "Nineteen"
Case Else
End Select
  • If the return value of the Val function from the previous step doesn’t satisfy the condition, then it returns the outputs as defined in this section.
  • Then, end the process.
Else
Select Case Val(Left(tens_text, 1))
Case 2: output = "Twenty "
Case 3: output = "Thirty "
Case 4: output = "Forty "
Case 5: output = "Fifty "
Case 6: output = "Sixty "
Case 7: output = "Seventy "
Case 8: output = "Eighty "
Case 9: output = "Ninety "
Case Else
End Select
  • Following that, we assigned the output with GetDigit.
  • Next, by using the Right function, we recovered the one’s position and then ended the function.
output = output & GetDigit _
(Right(tens_text, 1))
End If
GetTens = output
End Function
  • After that, we defined another function to convert the members from 1-9.
Function GetDigit(number)
  • Now, we selected the case using the Val function. It gives the number in a string and checks which case is appropriate for that. Else, it returns blank.
  • Subsequently, we ended the process.
Select Case Val(number)
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
  • Finally, we terminated the function.
End Function
			

Whoa! That was a really big code! Don’t worry. You’re almost there. Just hang on.

  • After writing the code, click on the Save icon as marked in the following image.

  • Following that, press the keyboard shortcut ALT + F11 to get back to the worksheet.

  • Now, enter the following formula in cell B5.
=spelling_number(B5)
  • After that, press ENTER.

Using created function to Spell Number Without Currency in Excel

As a result, you will have the following output on your worksheet.

  • Finally, by using the AutoFill option of Excel, you will get the remaining outputs as demonstrated in the image below.


What to Do If Spelling Number in Excel Is Not Working

While converting the numbers into text in Excel, sometimes we see that we are using a function like SpellNumber, but it is not working. It looks like the following image.

In most cases, it happens because you did not create this function in your own Visual Basic Editor. There is no in-built function in Excel to spell numbers. You have to write the VBA code and create your function.

To solve this issue, follow the steps mentioned in the 2nd method of this article, and consequently, you will get the following outputs on your worksheet.


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to Spell Number Without Currency in Excel


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to spell number without currency in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo