How to Convert Number to Text in Excel with Apostrophe

In this tutorial, we will demonstrate a quick but useful method to convert numbers to text format in Excel using the apostrophe. Furthermore, this method will help to prevent some common errors that occur when working with certain types of numerical data.

Why Would We Need to Convert Numbers to Text?

Usually, when we enter numbers in Excel, we want them to be in the number format, which is the default set by Excel. But for some types of numerical data, such as ID numbers, Account numbers, dates, and even cell phone numbers, we might need to format them as text. Some common problems that arise as result of formatting these data types as numbers are:

Excel will ignore a zero(0) in the leading position of a number.
Data with a large number of digits will be converted to exponential form.
In many cases, Excel will convert the 16th digit to zero(0) without an error warning.
Data that matches a valid Excel date format will be automatically converted to the corresponding date.

How to Convert Numbers to Text in Excel with the Apostrophe: Step-by-Step Procedure

Fortunately, we can easily convert numbers to text by adding an apostrophe as a prefix.

Step 1 – Entering Numbers in a Column

To begin with, enter the numbers to be converted to text.

In the sample dataset below, the number in cell B5 has a 0 in the leading position.

Press Enter.

The number in cell B5 no longer has the leading 0.

Step 2 – Adding an Apostrophe to Each Number

Now we’ll apply a custom text format to prevent Excel from automatically applying a number format to our data.

Click on cell C5 and type the same number as in cell B5, but add an apostrophe before the number.
Press Enter.

The number in cell C5 has 0 in the leading position.

So, in cell C5 the use of the apostrophe converted the number to text. Note the difference in the alignment of cells B5 and C5, i.e. cell C5 is left aligned because it’s text.

Add an apostrophe to each of the numbers below to convert them into text format too.

Step 3 – Inserting an Apostrophe into a Long Number

Data types that have a large number of digits can be problematic to handle as numbers. In this step, we’ll solve such a problem with the apostrophe prefix.

Enter a number with more than 15 digits in cell B8.
Press Enter.

Excel converts the long number into an exponential form.

To show all the digits as originally entered instead, simply add an apostrophe in front of the long number.

Press Enter.

The number now appears the same as it was entered, because it’s been formatted as text.

Converting Numbers to Text Using the TEXT Function

Excel’s built-in TEXT function can easily convert any data type to text format. This method is especially helpful when you want to make an existing set of numbers more readable.

Steps: