How to Spell Out Number Without Currency in Excel (2 Methods)

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.”

Code Breakdown

• We defined the name of the function as well as the value of the function.

Option Explicit
Function spelling_number(ByVal given_number)

• 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 "

• We excluded the extra spaces from the string by using the TRIM function.

given_number = Trim(Str(given_number))

• We used the InStr function to determine the place to set the position of the decimal point.

decimal_point = InStr(given_number, ".")

• 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

• 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.
• We used another IF statement to check the length of the given_number, whether it is greater than 3 or not.
• We increase the value of the variable count after each successful run of the loop.
• 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

• We assigned the values to the spelling_number function and ended the function.

spelling_number = us_dollars & us_cents
End Function

• We defined another function to convert a number from 100-999.

Function GetHundreds(ByVal given_number)

• We declared a variable named output as String.

Dim output As String

• 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)

• 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.
• 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

• We assigned the output value to the GetHundreds and ended the function.

GetHundreds = output
End Function

• We declared a variable named output as String.

Dim output As String

• We declared the output as null

output = ""

• 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.
• 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.
• 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

• We assigned the output with GetDigit.
• 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

• We defined another function to convert the members from 1-9.

Function GetDigit(number)

• 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.
• 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

• We terminated the function.

End Function