10 Ways to Convert Number to Text in Excel

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.

Add leading apostrophe
Add leading apostrophe

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.

Number to text using apostrophe
Number to text using apostrophe

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.

Error checking options
Error checking options

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.

Disable background error checking
Disable background error checking

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.

Convert text to columns
Convert text to columns

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.

Choose Delimiters
Choose Delimiters

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

Finish text to columns
Finish text to columns

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.

Concatenate formula
Concatenate formula

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
Number to text using ampersand
Number to text using ampersand

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

Using the fill handle
Using the fill handle

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 numbers and select Text
Highlight numbers and select Text

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.

Converted to text using Text format
Converted to text using Text format

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.

Using Format Cells
Using Format Cells

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.

Converted to text using Format Cells
Converted to text using Format Cells

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.

Using the TEXT function
Using the TEXT function

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

=TEXT(A2,"0")
Applying formula
Applying formula

Press Enter to get the text string.

TEXT function
TEXT function

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 with decimal places
TEXT with decimal places
=TEXT(123.25,"0.0")
TEXT with two decimal places
TEXT with two decimal places
=TEXT(123.25,"0.00")
TEXT with currency formatting
TEXT with currency formatting
=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.

Importing dataset to Power Query
Importing dataset to Power Query

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.

Create table
Create table

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.

Data in Power Query
Data in Power Query

You shall now see your dataset in Power Query.

Add custom column
Add custom column

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.

Text strings in Power Query
Text strings in Power Query

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

Close & Load To
Close & Load To

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

Existing worksheet
Existing worksheet

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.

Imported text in worksheet
Imported text in worksheet

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

Add to data model
Add to data model

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.

Create table Power Pivot
Create table Power Pivot

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

Whole number
Whole number

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.

DAX Formula
DAX Formula

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")
Apply DAX formula
Apply DAX formula

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.

Converted to text using Power Pivot
Converted to text using Power Pivot

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:

Excel VBA to convert to text
Excel VBA to convert to text
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
Obtain input dataset
Obtain input dataset

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

Select destination
Select destination

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

Converted to text using VBA
Converted to text using VBA

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:

VBA script to convert numbers to words
VBA script to convert numbers to words
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
Choose currency amounts
Choose currency amounts

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

Select destination for words
Select destination for words

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

Converted numbers to words
Converted numbers 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!

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃