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 to avoid some common errors that occur when working with certain types of numerical data.


Download Practice Workbook

You can download the practice workbook from here.


Why Do We Need to Convert Number to Text?

Whenever we are entering 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 number 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 onwards 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.

Step-by-Step Procedures to Convert Number to Text in Excel with Apostrophe

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 apostrophe. 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 number to text format in excel with apostrophe, 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.

Step-by-Step Procedures to Convert Number to Text in Excel with Apostrophe

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

Step-by-Step Procedures to Convert Number to Text in Excel with Apostrophe


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 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.

Step-by-Step Procedures to Convert Number to Text in Excel with Apostrophe

  • 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-by-Step Procedures to Convert Number to Text in Excel with Apostrophe


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.

Read More: How to Convert Number to Text with Commas in Excel (3 Easy Methods)


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.

Convert Number to Text Using Excel TEXT Function

  • Next, click on cell C5 and enter the following formula.
=TEXT(B5,0)
  • After that press Enter.

Convert Number to Text Using Excel TEXT Function

  • 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.

Read More: How to Convert Number to Text in Excel (4 Ways)


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.  And lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo