How to Convert Numbers to Text in Excel

Mathematically, leading zeros count as insignificant numbers. Excel, by default, gets rid of them when your cell is formatted as General or Number.

These numbers, however, do hold value in data like:

• Social Security Number
• Postal Code
• Student/Employee ID Number
• Phone Number

In such instances, you will need to convert these serial numbers to text in Excel.

You can utilize numerous cell formatting features for such conversion in Excel. Additionally, if all your numbers follow a specific format, use the TEXT function to arrange your numbers in that format.

Enter an Apostrophe Before the Number

When you enter an apostrophe before a value, Excel treats the data as text. While this method is best for when you’re just beginning to enter your data, there is a cheat code to use the method after data entry.

Enter only an apostrophe on an empty cell in your sheet. Once you hit enter, the symbol will seem like it disappeared, but fret not—it’s still there!

We will use the CONCAT function to combine the apostrophe with our numbers.

According to the sheet below, here’s the formula we used to enter an apostrophe before the numbers:

=CONCAT($F$2,E2)

From the Home Tab

You can change the data type of your cell/range from the Number section in the Home tab. Begin by selecting your cell or range, then follow these steps:

1. Select range C2:C11 then, go to Home.
   
2. Click on the fly-out in the Number section.
3. Choose Text.
   

If your values get pushed to the left, they’ve been converted to Text.

Use Format Cells Window

Excel offers a number of different formats to transform your values into. Even if your cell holds a number value, use the Format Cells utility to treat the stored value as text.

1. Select your range E2:E11.
   
2. On your keyboard, use Ctrl+1 to open Format cells.
3. Switch to Text.
   
4. Click OK.
   

Use the TEXT Function

The TEXT function in Excel allows you to convert values according to a set format. Using TEXT, format your number to add leading or trailing zeros, decimal points, or any additional characters.

Here’s the syntax TEXT uses in a formula:

=TEXT(value,format)

Arrange your numbers in whichever format you please with the TEXT function. Here are some of the examples using TEXT function:

Via Text to Columns

While Text to Columns is a dedicated tool to separate text from a cell into different columns, you can also use it to transform data types.

1. Select range B2:B11.
   
2. Go to the Data tab.
3. From the Data Tools section, click on Text to Columns.
   
4. Select Next > Next.
5. In Step 3 of 3 of Text to Columns Wizard, select Text under Column data format.
6. Specify a Destination. Make sure it’s different from the source data.
   
7. Select Finish.

Here’s how the final data looks like in range D2:D11

Transform Data in Power Query

Power Query is a powerful automation utility in Excel. If you need to convert multiple columns of numbers to text, using Power Query will be the fastest approach.

1. Select your data table.
2. Head to the Data tab.
3. From the Get and Transform section, select From Table/Range.
   
4. If prompted, click OK on the Create Table window.
   
5. From the Power Query Editor, go to Transform.
6. To select all columns, select the header of your first column, hold down on the Shift key then click on the header of the last column.
7. Select the fly-out next to Data Type in the Any Column section.
   
8. Choose Text.
9. Click on Replace current.
   
10. From the Home tab, click Close and Load.