## Dataset Overview

In Excel, you can spell out numbers using either a VBA custom function or a complex Excel formula.

Let’s say, we have some **Numbers **with **2 decimal points**. Our goal is to spell these numbers in words without mentioning any currency.

### Method 1 – Using an Excel Formula to Spell Numbers

**Enter the Formula:**- Start by entering the following formula in cell
**B5**:

- Start by entering the following formula in cell

`=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")`

- Press
**ENTER**after entering 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″)**→ Converts the number of cell**B5**into**000000000000.00**.**LEFT(TEXT(B5,”000000000000.00″))**→ Returns the left-most character from the formatted number to determine whether it is 0 or another value.**CHOOSE(LEFT(TEXT(B5,”000000000000.00″))+1,,”One “,”Two “,”Three “,”Four “,”Five “,”Six “,”Seven “,”Eight “,”Nine “)**→ Selects the appropriate text 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**→- Checks if the value is 0. If it is 0, we are not in the billions range, so nothing is displayed.
**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**→ Checks whether the following two digits are 0. If they are, it returns “**Hundred**”; otherwise, it returns “**Hundred****and**.”

By understanding this portion of the formula, you’ll be able to work with the rest of it effectively. The result will spell out the number in cell **B5** as shown in the image below.

- Use Excel’s
**AutoFill feature**to apply the formula to other cells.

**Read More:** [Solved] Spell Number Not Working in Excel

### Method 2 – Using Excel VBA to Spell Numbers

Utilizing Excel VBA (Visual Basic for Applications) is a clever approach to spell out numbers without currency in Excel. Follow the steps below to achieve this:

- Go to the
**Developer**tab on the**Ribbon**. - From the
**Code**group, select the**Visual Basic**option.

- The Microsoft Visual Basic window will open, as shown in the image below:

- In the Microsoft Visual Basic window, go to the
**Insert**tab. - Choose the
**Module**option from the drop-down menu.

- Enter 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 is satisfied, 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`

- Click on the
**Save**icon as indicated in the following image:

- Press the keyboard shortcut
**ALT + F11**to return to the Excel worksheet.

- In cell B5 (assuming
**B5**represents the cell in the**Numbers**column), enter the following formula:

`=spelling_number(B5)`

- Press
**ENTER**after entering the formula.

- You will see the spelled-out number in cell
**B5**on your worksheet.

- Use Excel’s
**AutoFill**feature to apply the formula to other cells, obtaining the remaining outputs.

## Troubleshooting – When Spelling Numbers Doesn’t Work

If you encounter issues with functions like “SpellNumber” not working, it’s likely because you haven’t created this function in your own Visual Basic Editor.

Excel doesn’t have a built-in function to spell out numbers directly; you need to write **VBA code** to create your custom function.

Follow the steps outlined in the **second method** of this article to address this issue and achieve the desired results on your worksheet.

**Read More:** How to Spell Number in Taka in Excel

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet.

**Download Practice Workbook**

You can download the practice workbook from here:

## Related Articles

- How to Spell Number in Dirhams in Excel
- How to Convert Number to Words in Excel
- How to Convert Number to Words in Excel Without VBA
- How to Convert Number to Words in Excel in Rupees
- How to Convert Peso Number to Words in Excel

**<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel**

i’m needing this exact thing, except i’m dealing with whole numbers with no “point”. i need for it to just say

5 = five

not

5 = five point zero zero

how do i adjust the code for that?

Hello MEGAN M,

Hope, you are doing well. Here’s the modified code below that will spell only whole numbers. Also, it will extract the whole number before spelling, if you insert decimal numbers.

Thanks and regards,

Md. Sourov Hossain Mithun

Team ExcelDemy.