How to Convert Number to Text in Excel with Apostrophe

In this tutorial, you are going to learn a quick but very important method to convert number to text format in Excel with apostrophe. Furthermore, this method will help you avoid some common errors that occur when working with certain types of numerical data.

Why Do We Need to Convert Number to Text?

Whenever we enter any number in Excel, most of the time we want them to be in the number format which is the default set by Excel. But for some types of numerical data, we might need to format them as text. Some examples include ID numbers, Account numbers, dates, and even cell phone numbers. Some common problems that arise by setting these above-mentioned types as numbers are:

Excel will ignore any zero(0) that comes in the leading position of a number.
Data that have a higher number of digits will be converted to exponential form.
In many cases, Excel will convert the 16th digit to zero(0) without giving any error warning.
When there is any data that matches with a valid Excel date format, Excel will automatically convert them to the relevant date even though you did not mean it.

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

So as you can see from the previous points, we really need some easy ways to convert our number to text format in Excel with apostrophes. In fact, in the following section, I am going to show you the quickest way to convert a number to text format with the help of an apostrophe. Let us go through the steps below.

Step 1: Entering Numbers in a Column

To convert numbers to text format in Excel with apostrophes, we need to insert a few data as a number in the first column.

To begin with, enter the numbers that you want to convert to text.
Note that in our dataset, the number I entered in cell B5 has a 0 in the leading position.

Next, press Enter.
Here, you should notice that the number in cell B5 no longer has the leading 0.

Step 2: Adding an Apostrophe to Each Number

After you have entered numbers in each cell manually, this step will show how you can prevent Excel from automatically setting the number format to data. Rather apply a custom text format as per our need.

Now, click on cell C5 and type in the same number as in cell B5 but add an additional apostrophe before the number.
Then, simply press Enter.

Immediately you can see that this time the number in cell C5 has 0 in the leading position.
Although we entered the same number in both the cells B5 and C5, excel converted the latter to text. But the number of cell B5 was in the number format.
Also, we can see the difference in the alignment of both cells. That is, cell C5 has left alignment because it’s a text.
Similarly, add an apostrophe to each of the numbers and Excel will convert them into a text format.

Step 3: Inserting Apostrophe into a Long Number

In our real life, we have many data types that have a large number of digits. As a result, these data types can be very problematic to handle. In this step, you will see a practical example of how to solve this type of problem.

Firstly, enter a number that is more than 15 digits long in cell B8.
Next, press Enter.

Now, as you can see in the image below, Excel has converted the long number into an exponential form. This is not what we want in most cases.

Since we want to show all the digits, simply add an apostrophe in front of the long number.

After that press Enter.
Finally, you should be able to see all the digits in the number that you entered because Excel has now formatted the number as text.

Convert Number to Text Using Excel TEXT Function

Excel has a built-in TEXT function that can easily convert any data type to text format data. This method is especially helpful when you already have a set of numbers and want to make them more readable. Let us see how we can apply this function.

Steps:

To start with, enter the desired numbers in the cells B5 to B8.
However, the numbers will be right aligned because Excel has by default applied the number format to them.

Next, click on cell C5 and enter the following formula.

=TEXT(B5,0)

After that press Enter.

Here, the number you entered will now be in the text format because we used the TEXT function.
Also, you can see that the number in cell C5 is left aligned which denotes text data.
Now, click and drag the Fill handle to apply the TEXT function to the remaining cells below.

Finally, similar to the data in cell C5, the remaining cells in column C will have a text format.

Download Practice Workbook

Convert Number to Text with Apostrophe.xlsx

Conclusion

I hope that you thoroughly understood the method I showed in this brief tutorial. Although the method looks pretty simple, many people who are not familiar with this become prone to some common errors. So, I really encourage you to practice this method on various types of datasets. If you find any error, simply review the method I shared above.

If you have any queries, please let me know in the comments.

<< Go Back to Excel Convert Number to Text | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!