This easy and elaborate Microsoft Excel tutorial will help you learn how to store numbers as text in Excel with steps and screenshots.

Numbers stored as text in Excel help you to preserve leading zeroes, avoid unintended calculations, prevent auto-date formatting, run text-specific functions, prevent scientific notation, and many more.

Suppose you don’t know the steps for Excel numbers formatted as text, nothing to worry about! Follow along with the tutorial until the end to discover all such tried and tested techniques. Let’s begin!

📒 Read More: 7 Ways to Convert Text to Numbers in Microsoft Excel

Using the Leading Apostrophe Method

This is the quickest technique. If you’re working on a tiny dataset, like entering a couple of bank account numbers or telephone numbers, you should try this out.

In a cell, enter an Apostrophe symbol and then type the digits.

Hit Enter. Excel will convert the value to a text string.

You’ll see that the entered number now has a left alignment along with an error indicator (solid green triangle in the top left corner).

Using the Number Formatting Command

Suppose, you need to import a large dataset containing many numeric values through the copy-and-paste method. The default number formatting system of Microsoft Excel will convert some values to dates, delete leading zeroes from some cells, automatically convert large numbers to scientific notations, and so on.

If you wish to avoid such inconvenient auto-formatting, you must format the destination cell ranges with specific formatting, known as Text. If you do so, imported values will be pasted as text strings rather than numbers.

In the target worksheet, identify the column or cell range where you’d like to copy and paste the imported numbers. Let’s say, you’re importing from a Notepad file.

After selecting the destination cell range, click on the General drop-down menu in the Number commands block of the Home tab.

You’ll see the Text format option at the bottom of the General drop-down context menu. Click on that.

Excel will format the selected cell range with the Text formatting code.

Alternatively, select the destination and press Ctrl + 1 to bring up the Format Cells dialog box.

Click on the Text option in the left-side navigation panel and below the Category column.

Click OK to apply text formatting.

Now, simply copy the target dataset from Notepad using Ctrl + C keys.

Paste it onto the previously selected cell range by pressing Ctrl + V.

Excel will store the imported dataset of numbers in text format.

Using the Paste Special Tool

Sometimes, you might not know the exact number of columns and rows needed to paste the imported dataset. When the dataset being imported contains hundreds and more rows, it’s also not appropriate to count the rows and columns so you can apply text formatting before pasting the dataset. Here comes the Paste Special tool at your rescue.

You can use the Paste Special tool in real-time to choose the type of destination formatting you want, like the Text formatting option.

Copy the number dataset from the source file, like Notepad.

Select the destination cell in the worksheet and press Ctrl + Alt + V.

You’ll see the Paste Special dialog box. Choose Text from the list of formats available and click OK.

That’s it! You’ve successfully stored numbers as text in Excel.

Using the Ampersand Symbol

Suppose, there’s a pre-existing dataset of numbers in your worksheet. However, the autoformat policy of Excel might have changed some numbers to dates or scientific formats. In this scenario, you can use the Ampersand sign to concatenate the numbers with empty text references. Excel will transform the numbers into text strings.

For example, your source dataset looks similar to the above screenshot.

You want to convert the numbers to text strings in column B.

In B2, enter the following formula and hit Enter:

=A2&""

You should see the text version of the source number values.

Drag the fill handle from B2 down to the last cell in column B that has data in the adjacent column A.

Excel will replicate the formula of B2 and paste it into all the cells falling within the fill handle selection range.

A downside of this method is that it won’t correct numbers that were subjected to automated leading zero removal. Since Excel has already removed the leading zeroes when you created the number dataset, the concatenate technique won’t be able to figure out how many leading zeroes there were.

Using the Text to Columns Tool

The Text to Column tool is another intuitive option to store numbers as text. You can use it to either split a large number in two or more cells or in full length.

For example, you’re creating a text-based database of product package codes that includes manufacturing unit ID, year, and batch number in a 15-digit long numeric value.

Now, from the central database server, product catalog, or the Manufacturing Execution System (MES) you’ve downloaded these codes.

You’ve pasted these values in the Package Code column. Also, for data organization, you’ve created three additional columns, namely MFG Unit (4 Digits), Year (4 Digits), and Batch (7 Digits). You can refer to the screenshot shown above.

If this is what your source worksheet looks like, select all of the package code values in column A.

Click on the Text to Columns command inside the Data Tools block of the Data tab.

The Convert Text to Columns Wizard will show up. Select the Fixed width radio button in the Original data type section. Click Next to proceed.

The next window is to preview the source dataset. Here, in the Data preview panel, you can click after a few intervals of digits to split the input dataset into small fractions of codes.

Considering the current dataset, you must click after the first and second sets of 4 digits. This should split the code into 4, 4, and 7-digit sets. Click Next to go ahead.

Below the Column data format section, select the Text option.

Delete the contents of the Destination field. Now, select the cell range where you want to populate the individual codes. According to the dataset in this exercise, the cell range should be $B$2:$D$6.

Click the Finish button.

The Text to Columns tool will split the contents as configured.

The first column will be automatically formatted with the Text formatting rule.

However, the remaining two columns will still be in the General or Number format. So, select those columns and press Ctrl + 1 to show the Format Cells dialog.

Go to the Number tab and click on the Text category on the left.

Excel will format the input cell range with the Text formatting code.

You can now delete the source dataset of package codes.

Using the Excel TEXT Function

Suppose, you’ve got a dataset containing dates in serial numbers. You wish to convert these values to dates and store them in a text format. In this case, you can use the TEXT function. This formula also allows you to customize the formatting the display style of the number you’re converting.

In the cell, where you want to get the text value, paste the following formula and hit Enter:

=TEXT(A2,"dd-mm-yyyy")

Don’t forget to replace A2 with the actual cell address from your worksheet.

You should see the calculated text date in the first cell.

Now, use the fill handle of that cell and drag it down to populate results for the rest of the dataset.

Using Power Query

If you’re importing a dataset to an Excel worksheet from an external database and you only need the final data in text rather than in number format, Power Query is the best method to try.

To open the input dataset in Power Query, click on the Get Data drop-down menu in the Get & Transform Data commands block of the Data tab.

You’ll see various options, like From Database, From Azure, etc., options. Hover the mouse cursor over the appropriate service.

This will open an overflow menu on the right. From there, you can select the specific database, like From SQL Server Database.

You should now see your data in the Power Query Editor interface.

There are two approaches to converting and storing numbers in the text format.

If You Don’t Need Source Data

Double-click on the column of the source data to change the header if needed. For example, I’ve changed Numbers to Texts.

Hit Enter to save the changes made to the column header.

Now, select the column again and navigate to the Transform tab. Click on the Data Type drop-down menu and choose Text from the context menu.

Power Query will apply the text formatting instantly.

If You Need the Source Data

Navigate to the Add Column tab. There, click on the Custom Column command to open the Custom Column wizard.

Enter the column header into the new column name field.

Into the Custom column formula field, enter the following formula:

Text.From([Numbers])

Click OK to create the new column with corresponding text strings for the number values.

You should see the new column in Power Query.

To export the transformed data from Power Query to Excel, click on the File tab and choose Close & Load To from the context menu.

The Import Data dialog box will pop open. There, choose the Existing worksheet as the sheet destination.

Delete the content of the Existing worksheet field and choose a new destination cell range using the mouse or manually entering the address.

Click OK to import data to Excel.

Using Excel VBA

Excel VBA allows you to store numbers as text in Excel programmatically. Here, you can use a script to create a VBA macro. Then, you can run the macro and interact with a few prompts to achieve the data transformation you require.

Firstly, find out the steps to create a VBA macro in this quick tutorial:

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

Enter the Number From a Source

You can use the following script to enter comma-separated numerical values in a prompt box. Then, the macro will transform the numbers into texts and paste the results into a designated cell range you select.

Sub StoreNumbersAsText()
    Dim strEntries As String
    Dim inputArray() As String
    Dim destRange As Range
    Dim i As Long
    
    ' Prompt for comma-separated values
    strEntries = Application.InputBox("Enter multiple comma-separated values:", "Entries", Type:=2)
    If strEntries = "False" Then Exit Sub ' User canceled
    
    ' Split the input into an array
    inputArray = Split(strEntries, ",")
    
    ' Prompt for the destination cell range
    On Error Resume Next
    Set destRange = Application.InputBox("Enter the destination cell range:", "Destination Range", Type:=8)
    On Error GoTo 0
    
    If destRange Is Nothing Then
        MsgBox "Invalid destination range. Please try again.", vbExclamation
        Exit Sub
    End If
    
    ' Store each value as text in the destination range
    For i = LBound(inputArray) To UBound(inputArray)
        destRange.Cells(i + 1).Value = inputArray(i)
        destRange.Cells(i + 1).NumberFormat = "@"
    Next i
    
    MsgBox "Numbers stored as text in the specified range.", vbInformation
End Sub

You can use the Alt + F8 shortcut key to launch the Macro dialog. There, select the StoreNumbersAsText macro and click Run to execute.

Now, you’ll see a series of prompt boxes that’ll guide you through the entire process as shown below:

• An input box where you can enter the numbers separated by commas.

• Another input box so you can choose the destination cell range.

Excel will store the entered numbers in the worksheet as text.

Format Numbers to Text Strings

You can use the following script to choose an input dataset from a column in the workbook or a different file.

Sub ConvertNumbersToText()

    Dim inputRange As Range
    Dim destinationRange As Range
    Dim lastRow As Long

    ' Get input data range using mouse selection
    Set inputRange = Application.InputBox(Prompt:="Select the range containing numbers to convert (use mouse)", Type:=8)
    If inputRange Is Nothing Then Exit Sub ' User cancelled selection

    ' Get destination range using mouse selection
    Set destinationRange = Application.InputBox(Prompt:="Select the destination range for text output (use mouse)", Type:=8)
    If destinationRange Is Nothing Then Exit Sub ' User cancelled selection

    ' Get the last row of the destination range
    lastRow = destinationRange.Row + inputRange.Rows.Count - 1 ' Adjust for number of rows in input

    ' Loop through each cell in the input range
    For Each cell In inputRange
        ' Convert number to text and store in a variable
        textValue = CStr(cell.Value)

        ' Paste the text value in the destination range
        destinationRange.Value = textValue

        ' Move to the next cell in the destination range
        Set destinationRange = destinationRange.Offset(1, 0) ' Move down one row
    Next cell

    ' Clear any extra pasted values after the last row
    destinationRange.Resize(lastRow - destinationRange.Row + 1).ClearContents

    MsgBox "Conversion complete!", vbInformation

End Sub

The macro will show the following prompts:

• Enter input dataset cell range.

• Supply the cell range for the destination.

The above is a screenshot showing the performance of this script.

Conclusions

So far, you’ve learned how to store numbers as text in Excel using convenient methods, like adding a leading apostrophe before entering the numerical values, joining the number with a blank reference using the ampersand symbol, using the text formatting rule before pasting datasets, Text to Columns, and so on.

You’ve also learned two fully automatic methods to store Excel numbers as texts using an Excel VBA macro. Give any or all of the above methods a try to figure out which one is the most suitable considering the use case and your Excel expertise level.

Was this Excel tutorial helpful? Comment below. If you’d like to suggest a better method, add that in your comment.