How to Convert Number to Text in Excel?

In this article, we will explain how to
– Convert numbers to text in Excel.
– Use an apostrophe to keep leading zeros.
– Use the TEXT function to deal with numbers having different formats.
– Apply the Format Cells feature to precisely change the number formatting to text.
– Use the Text to Columns option for more flexibility.
– Convert the numbers into words with VBA.
– Use the VLOOKUP function to avoid the error for different formats.

Excel won’t keep leading zeros in numbers. But let’s say you need to keep the same length for all numbers to understand the data easily. Or, say you are preserving phone numbers, ZIP codes, any kind of numeric codes, or SSNs. Then you must convert the numbers into text for leading zeros.

When you are working with currency terms, then you must use the words format of the numeric values to avoid mistakes. Using the text format of a number makes them easier to read, especially when there is no need to do calculations.

Here, we have used Microsoft 365 to prepare this article. However, you can do the same for other versions also.

Download Practice Workbook

How to Convert Number to Text in Excel? (4 Suitable Methods)

Excel offers various techniques and functions for the conversion of numeric values to text values. Here, we will discuss four suitable methods for converting numbers to text in Excel. They are:

• Using the TEXT function.
• Applying Apostrophe.
• Use of Format Cells feature.
• Using the Text to Column Wizard.

Whether you’re dealing with budgets, invoices, or any numerical data, you may need to convert numbers to text. Let’s see below how to deal with different situations.

1. Use of TEXT Function to Convert Number to Text

By default, the TEXT function converts any form of value to text. So, the number is not an exception. Not only that, but we can also use this function to get the text in a specific format also. Below, you can see some details about this TEXT function.

• You may need to add a leading zero or add currency, or you may need to convert the number into text with decimals. You can get all of these formats with the TEXT function.
• Use the version formula that you need. I have attached the formulas and output below.

Note: Generally, Excel keeps the numbers on the right side of the cell and texts on the left. So, if the cell value alignment moved to the left then your numeric values are no more in number form, they became text format.

2. Converting Number to Text with Apostrophe

In this part, we will convert the number to text with an apostrophe. Adding an apostrophe before any cell data in Excel also converts it into text. We can use this to convert a number to text as well.

• Write an apostrophe before the number and get the text format of the number.

3. Use of Format Cells Feature for Converting Number to Text

Excel offers the Format Cells dialog box with a wide range of formatting options. Selecting proper options, we can use this to change numbers to text too.

• Select the numbers >> press CTRL+1 >> so you will see the Format Cells dialog box.
• In that dialog box >> go to the Number tab >> from Category, choose Text option >> press OK.

• You will get all the numbers in text format.

4. Using Text to Column Wizard to Convert Number to Text

The main feature of this wizard is to split a cell into multiple columns. In the end, it wants the type of the output data. If we don’t choose to split numbers into different columns and select the data type to text, we can change them into such with the feature.

• Select the target numbers >> from the Data tab >> click on Text to Columns.

• Select Delimited and press Next to the Convert Text to Columns Wizard – Step 1 of 3.
• Similarly, press Next to the Convert Text to Columns Wizard – Step 2 of 3.

Click here to enlarge the image.

• Lastly, in the Convert Text to Columns Wizard – Step 3 of 3 >> from Column data format >> select Text >> press Finish.

Click here to enlarge the image.

Below, I have attached the changed format of the number.

How to Apply Excel VLOOKUP Function to Avoid Error of Mixing Different Formats?

The VLOOKUP function won’t return the correct value because of the mixing of text and number format. Here, our lookup value (defined ID) is in the number format and our lookup column (IDs) has text format. That’s why, if you use this VLOOKUP function directly then it couldn’t give us the result we wanted. So, we have to convert the number for getting the actual result.

How do we do that? We can use any of the methods described above within the formula, so the VLOOKUP function picks it up as a text.

• In this case, enter the following formula.

Here, the TEXT function will convert the number of the D17 cell into text format. Then the VLOOKUP function will search for this text formatted D17 value within B5:E14 cells and return the corresponding value of the 3rd column (D column) as the result.

How to Convert Number to Words with Excel VBA?

Let’s say we want to change 1 into “one”, 2 into “two”, and so on. We will need to use VBA for that.

• From your worksheet >> under the Developer tab >> click on Visual Basic >> after opening the Visual Basic Editor >> from Insert tab >> open Module.
• Copy-paste the given VBA code into the Module.

Click here to enlarge the image.

Function Convert_Number_into_word_with_currency(ByVal whole_number)
Dim converted_into_dollar, converted_into_cent
my_ary = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
whole_number = Trim(Str(whole_number))
x_decimal = InStr(whole_number, ".")
If x_decimal > 0 Then
    converted_into_cent = get_ten(Left(Mid(whole_number, x_decimal + 1) & "00", 2))
    whole_number = Trim(Left(whole_number, x_decimal - 1))
End If
xIndex = 1
Do While whole_number <> ""
    xHundred = ""
    xValue = Right(whole_number, 3)
    If Val(xValue) <> 0 Then
        xValue = Right("000" & xValue, 3)
        If Mid(xValue, 1, 1) <> "0" Then
            xHundred = get_digit(Mid(xValue, 1, 1)) & " Hundred "
        End If
        If Mid(xValue, 2, 1) <> "0" Then
            xHundred = xHundred & get_ten(Mid(xValue, 2))
        Else
            xHundred = xHundred & get_digit(Mid(xValue, 3))
        End If
    End If
    If xHundred <> "" Then
        converted_into_dollar = xHundred & my_ary(xIndex) & Dollar
    End If
    If Len(whole_number) > 3 Then
        whole_number = Left(whole_number, Len(whole_number) - 3)
    Else
        whole_number = ""
    End If
    xIndex = xIndex + 1
Loop
Select Case converted_into_dollar
    Case ""
        converted_into_dollar = " Zero Dollar"
    Case "One"
        converted_into_dollar = " One Dollar"
    Case Else
        converted_into_dollar = converted_into_dollar & "Dollars"
End Select
Select Case converted_into_cent
    Case ""
        converted_into_cent = " and Zero Cent"
    Case "One"
        converted_into_cent = " and One Cent"
    Case Else
        converted_into_cent = " and " & converted_into_cent & "Cents"
End Select
Convert_Number_into_word_with_currency = converted_into_dollar & converted_into_cent
End Function
Function get_ten(pTens)
Dim my_output As String
my_output = ""
If Val(Left(pTens, 1)) = 1 Then
    Select Case Val(pTens)
        Case 10: my_output = "Ten"
        Case 11: my_output = "Eleven"
        Case 12: my_output = "Twelve"
        Case 13: my_output = "Thirteen"
        Case 14: my_output = "Fourteen"
        Case 15: my_output = "Fifteen"
        Case 16: my_output = "Sixteen"
        Case 17: my_output = "Seventeen"
        Case 18: my_output = "Eighteen"
        Case 19: my_output = "Nineteen"
        Case Else
    End Select
Else
Select Case Val(Left(pTens, 1))
    Case 2: my_output = "Twenty "
    Case 3: my_output = "Thirty "
    Case 4: my_output = "Forty "
    Case 5: my_output = "Fifty "
    Case 6: my_output = "Sixty "
    Case 7: my_output = "Seventy "
    Case 8: my_output = "Eighty "
    Case 9: my_output = "Ninety "
    Case Else
End Select
my_output = my_output & get_digit(Right(pTens, 1))
End If
get_ten = my_output
End Function
Function get_digit(pDigit)
Select Case Val(pDigit)
    Case 1: get_digit = "One"
    Case 2: get_digit = "Two"
    Case 3: get_digit = "Three"
    Case 4: get_digit = "Four"
    Case 5: get_digit = "Five"
    Case 6: get_digit = "Six"
    Case 7: get_digit = "Seven"
    Case 8: get_digit = "Eight"
    Case 9: get_digit = "Nine"
    Case Else: get_digit = ""
End Select
End Function

• Now, save the code >> go back to the worksheet >> in D5 cell >> use the created VBA function.

• Press ENTER.

Practice Section

Now you can practice by yourself.

Frequently Asked Questions

1. How do I convert numbers to text in Excel without formatting?

You can use the TEXT function for conversion numbers to texts. Say, you have a number in the A1 cell. Then use the following formula: =TEXT(A1,0).

2. How to convert a formula to value in Excel without the Paste Special?

To convert a formula to value, press F2 >> then press F9 >> then click ENTER.

3. What is the fastest way to convert numbers to text in Excel?

Using an apostrophe before the number is the fastest way to convert numbers to text in Excel.

Conclusion

I hope all your confusion on how to convert a number to text or words in Excel is now clear now. Here, I have explained some different ways to convert the numbers into text. So, you can work with your preferred one. We have covered using an apostrophe, TEXT function, Format Cells, and Text to Column features for that. Still, if you have any queries then don’t hesitate. Feel free to communicate with us through comments or email. You can visit our site for more Excel-related articles.

Excel Convert Number to Text: Knowledge Hub

<< Go Back to Learn Excel