Do you want to learn how to convert number to text in Excel to appropriately deal with situations like entering and visualizing a large numeric value, showing leading zeroes, or stopping Excel from converting numeric entries to dates? Keep reading!

Though Excel is primarily popular for crunching numbers and creating actionable insights from raw datasets, it allows you to convert numeric datasets to special formats like dates, currency, etc. One of the most popularly used formatting is number-to-text conversion to transform decimals into text strings.

For example, you can’t search certain numeric combinations like how many times 500 appears in the dataset if all the values are in number format and entries are more than 3 digits long. Here, converting the dataset to text would help to search 500 in the cells of your dataset. There are more such examples where a text format of the given number is more useful.

Find below various ways for number-to-text conversion for convenient data analytics in Excel.

Convert Number to Text Using Apostrophe

If you want to convert a few numbers to text in Excel, you can use this quick manual method. Here, you simply need to add a leading apostrophe in the cell to transform numerical values into text strings.

To do this, select the cell in question and press the F2 key to enter cell editing mode.

Click on the first character to the left of the value and add the apostrophe symbol.

Hit Enter and Excel shall automatically convert your number to text in no time.

You must repeat the step separately for each cell in your dataset.

You’ll see the background error checking icon in the cell. You can ignore it.

However, if the Excel worksheet is meant for presentation in a professional meeting or event, you can hide the background error checking icon. Simply click on the cell and click on the yellow warning icon on the right-hand side.

A context menu will open with 6 options. Click on the last one which is Error Checking Options.

The Formulas settings for the Excel Options dialog shall show up. There, uncheck the Enable background error checking checkbox and click OK to save the changes.

Convert Number to Text Using Text to Columns

While the Text to Columns tool allows you to split delimited datasets like comma-separated values (CSV), semicolon-separated values, and tab-separated values into multiple columns, it can also be used to transform numerical values into texts.

This method is particularly suitable when you must convert a whole column filled with numerical data to text strings.

Go to the source worksheet and highlight the dataset in the column. Now, press Alt + A + E to bring up the Text to Columns wizard.

Choose the Delimited option and click Next.

On the next screen of the dialog box, choose any option below the Delimiters column and click Next.

Below the Column data format column, choose Text and click the Finish button.

As soon as you complete all the abo steps, Excel shall convert the highlighted numericals into text strings.

If background error checking is active on your Excel desktop app, you should see green error checking icons on the cells of the dataset.

Convert Number to Text Using Ampersand

In Excel, you can use the & (Shift + 7) operator to concatenate, or join, text strings together. When joining text strings, if you add a reference to a number cell and text cell, that’ll also work.

Suppose, you want to convert all the numbers in a column to text using this method, go to the first cell (B2) of the blank column (column B) to the immediate right side of the source dataset column (column A). There, type the following formula:

=""&A2

Hit Enter to complete the number-to-text conversion.

Now, drag the fill handle down the column until the last cell where data exists in the source dataset parallel to column B.

Excel shall quickly copy the formula of the first cell in all the cells of the column and calculate to generate the text strings.

In this method, you don’t see the background error checking icon in the converted cells.

Number to Text Using the Number Formatting Tool

If your dataset containing numbers has values not exceeding 11 characters, you can convert those to text strings using the Number formatting tool in the Home tab.

Highlight the target dataset and go to the General drop-down in the Number block. Click on that and choose Text from the context menu.

You should get your text strings in the same column. The entries in the cells shall align to the left, transforming to text values.

Convert Number to Text Using the Format Cells Tool

This is another quick method to convert massive datasets to text strings.

Go to the target dataset and highlight that.

Press Ctrl + 1 to open the Format Cells dialog.

There, select the Text option below the Category column and click OK.

This way, Excel converts numbers to text without showing the tiny green error checking icon.

Convert Number to Text Using the TEXT Function

You can also use various Excel formulas to get text strings from numerical values. One such is the TEXT function. Using this formula, you can also introduce custom formatting for the output values.

On the target dataset, highlight the first cell and enter the following formula:

=TEXT(A2,"0")

Press Enter to get the text string.

You can now use the fill handle to apply the same formula to the rest of the cells in the column.

To introduce custom formatting in your text string values, you can use the following variations of the TEXT formula:

=TEXT(123.25,"0.0")

=TEXT(123.25,"0.00")

=TEXT(D2,"$#,###,###.##")

Convert Number to Text Using Power Query

You can also use the Power Query tool to transform imported or existing datasets to text strings from numbers in bulk. If you’ve got Excel 2010 or better, you can use this method.

If you’re importing a large numerical dataset from an external source, you can go to the Data tab and click Get Data to find various options to import data.

There, hover the cursor over the From Database option and choose your source from options like From SQL, From Microsoft Access, From Oracle, and so on.

For datasets already available in your worksheet, highlight that and click on the From Table/Range command in the Data tab. Click OK on the Create Table Dialog to form the table.

You shall now see your dataset in Power Query.

Go to the Add Column tab and click on the Custom Column button.

In the Custom Column dialog, type a name in the New column name field.

In the Custom column formula field, enter the following formula and click OK:

Text.From([CC Numbers])

In the above formula, change the [CC Numbers] column name according to your own dataset.

Power Query shall create a new column containing the text strings of the corresponding numerical values.

Now, click on the File tab and choose the Close & Load To option.

The Import Data dialog shall show up. There, select the Existing worksheet option and highlight the cell where you’d like Excel to import converted datasets from Power Query.

You may delete the redundant columns containing the numerical values as the TEXT Strings or the custom column you created contains the values you need.

Convert Number to Text Using Power Pivot

Power Pivot is another option for you to convert a large numerical dataset to text strings. You should use Power Pivot in scenarios where you must import a big dataset that contains more than 1,048,576 rows that Excel can handle. Now, you can cleanse and transform your dataset to export that to the actual Excel spreadsheet which should be within the row limit in Excel.

If you don’t see the Power Pivot tab on the Excel ribbon menu, read this article to add the tool to your Excel desktop app:

📒 Read More: 4 Ways to Add Power Pivot to Microsoft Excel

Now that you’ve got the tool, highlight the dataset you want to analyze in Power Pivot and click on the Add to Data Model button inside the Power Pivot tab.

You shall see the Create Table dialog. Click OK to go ahead.

On the Power Pivot window, click on the column header of the input dataset. On the Home tab, click on the Data Type button and choose Whole Number inside the Formatting block.

Now, click on the first cell of the column adjacent to the input dataset and enter the following Data Analysis Expressions (DAX) formula in it:

=FORMAT(Table5[CC Numbers],"0")

Hit Enter. Power Pivot shall use the formula to transform all the numerical data in the left-side column into text strings in the right-side column. Don’t forget to customize the formula according to your own dataset.

Highlight the converted values from the Power Pivot column and paste them on your Excel spreadsheet.

Convert Number to Text Using Excel VBA

If you wish to automate the process of converting numbers to text strings, you can use Excel VBA.

Find below a script that you can use to create a VBA macro that interactively handles the task:

Sub ConvertNumbersToText()

    ' Prompt the user to select a range of cells with numerical data
    Dim SourceRange As Range
    Set SourceRange = Application.InputBox("Select a range with numerical data", "Obtain Range Object", Type:=8)

    ' Prompt the user to select a range where to paste the converted data
    Dim TargetRange As Range
    Set TargetRange = Application.InputBox("Select a range where to paste the converted data", "Obtain Range Object", Type:=8)

    ' Check if the selected ranges have the same size
    If SourceRange.Cells.Count <> TargetRange.Cells.Count Then
        MsgBox "The source and target ranges must have the same size.", vbCritical
        Exit Sub
    End If

    ' Convert number formatting of input data to text format and paste the values in the designated cell range
    Dim cell As Range
    Dim i As Integer
    For i = 1 To SourceRange.Cells.Count
        Set cell = SourceRange.Cells(i)
        TargetRange.Cells(i).NumberFormat = "@"
        TargetRange.Cells(i).Value = Format(cell.Value, "0")
    Next i

    MsgBox "Conversion completed successfully.", vbInformation

End Sub

Now, when you execute the macro, you’ll see a dialog box that allows you to select cell ranges containing numerical datasets as inputs.

Next, you should see another dialog that allows you to choose a destination cell range.

Finally, the VBA macro completes the conversion process. You might see the green error-checking icons once the conversion is done.

To learn how to create a VBA macro using the above script, read the following article:

📒 Read More: How To Use The VBA Code You Find Online

Convert Currency Notation to Text Using Excel VBA

Do you often need to convert numerical currency notations into figures for check issuing or banking purposes?

Then, you can use the following VBA script to interactively convert numerical currency amounts to figures in a single click:

Sub ConvertNumbersToWords()
    Dim rngInput As Range
    Dim rngDestination As Range
    Dim cell As Range
    Dim inputCell As Variant
    Dim outputCell As Range
    
    ' Prompt user to select input range
    On Error Resume Next
    Set rngInput = Application.InputBox("Select the range containing numerical values:", Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels the input box
    If rngInput Is Nothing Then
        Exit Sub
    End If
    
    ' Prompt user to select destination range
    On Error Resume Next
    Set rngDestination = Application.InputBox("Select the destination cell range:", Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels the input box
    If rngDestination Is Nothing Then
        Exit Sub
    End If
    
    ' Loop through each cell in input range
    For Each cell In rngInput
        ' Check if cell contains a numerical value
        If IsNumeric(cell.Value) Then
            ' Convert numerical value to words
            inputCell = cell.Value
            Set outputCell = rngDestination.Cells(cell.Row - rngInput.Row + 1, cell.Column - rngInput.Column + 1)
            outputCell.Value = ConvertToWords(inputCell) & " dollars"
        End If
    Next cell
End Sub

Function ConvertToWords(ByVal number As Double) As String
    Dim numText As String
    Dim units As Variant, tens As Variant, teens As Variant
    
    units = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine")
    tens = Array("", "ten", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety")
    teens = Array("eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen")
    
    If number = 0 Then
        ConvertToWords = "zero"
    ElseIf number < 10 Then
        ConvertToWords = units(number)
    ElseIf number < 20 Then
        ConvertToWords = teens(number - 11)
    ElseIf number < 100 Then
        ConvertToWords = tens(Int(number / 10)) & " " & units(number Mod 10)
    ElseIf number < 1000 Then
        ConvertToWords = units(Int(number / 100)) & " hundred " & ConvertToWords(number Mod 100)
    ElseIf number < 1000000 Then
        ConvertToWords = ConvertToWords(Int(number / 1000)) & " thousand " & ConvertToWords(number Mod 1000)
    ElseIf number < 1000000000 Then
        ConvertToWords = ConvertToWords(Int(number / 1000000)) & " million " & ConvertToWords(number Mod 1000000)
    ElseIf number < 1000000000000# Then
        ConvertToWords = ConvertToWords(Int(number / 1000000000)) & " billion " & ConvertToWords(number Mod 1000000000)
    Else
        ConvertToWords = "Number too large"
    End If
End Function

First, you need to select the input dataset as shown above.

Then, you also need to choose the destination for currency notations converted to words.

The above image shows converted words.

Conclusions

There you go! These are the methods you’ve been looking for if you wish to convert numbers to text in Excel. For small-scale number-to-text conversion, you can use the Excel app interface commands like Text to Columns, Number formatting, Format Cells, etc.

For large-scale dataset importing and transformation, you should go for Power Query and Power Pivot.

Finally, if you wish to automate the process, create VBA macros using the scripts given in this article.

If the article helped you or you’d like to share feedback, do comment in the box below!