Excel is always everyone’s favorite tool when it comes to using it as a database or data entry and many data analysis is also using this free and powerful tool.
Table of Contents
- Why Do We Have To Convert Numbers To Text In Excel?
- Convert Number To Text In Excel [4 Proven Methods]
Here we are having much default settings that we shouldn’t mess up with but the time it is necessary to mess up with them as we need them.
Convert Numbers To Text In Excel also falls under this situation, now we are going to see the different methods by which you can easily convert numbers to text in excel.
Before that, it is necessary to understand why we have to change the numbers to text. Here we are going to see the different scenarios.
Why Do We Have To Convert Numbers To Text In Excel?
In some cases, there is no need to convert numbers to text in excel but in some cases, it really required now we are going to head to those cases one by one.
Leading Zeros: Suppose you are entering a number like 0000001 in excel. But as you enter, the number shorted to 1. Excel thinks it is not necessary to put those leading zeros. Trust me at some point you will face this issue because in some cases Leading Zero require.
Large Numeric Value: For those who don’t know let me tell you that excel can take only those numeric values that are 15 digits long. When you tried to put 16-digit large numeric values it converts them to 0.
When you are dealing with some IDs or other document details that are having large numeric values of more than 15 then it is required to convert numbers to text in excel.
Excel Convert Numbers To Date: It is a quite popular problem that you might have already. Whenever you write the number in 10-11 format, excel automatically converts it into a date. In my case, 10-11 converted into 10 November and the current year automatically.
In order to overcome this problem, it is necessary to convert the number to text in excel.
Convert Number To Text In Excel [4 Proven Methods]
So finally we are here to solve your problem, here we are going to describe 4 proven methods that will help you to convert numbers to text in excel.
1. Cell Formatting Method
This is the easiest method to convert numbers to text. Follow the below steps in order to perform the cell formatting method.
- Your First step is to select the range of numbers, you can also select one single cell as well. After selecting the range as shown below the next step is to right-click on the range. There is a popup window that appears where you can find the option of “Format Cells”.
- As soon as you click on “Format Cells” a new window will appear on the screen. All you need is to select the “Text” as shown in the below image. Then click on “OK” in order to apply the changes.
- In this step, your result will appear on your screen. As you can clearly see below that all the selected numeric values alignment changes from right to left.
The cell Formatting Method is one of the easiest methods to deal with. Everybody can easily follow this step and convert numbers to text in excel. Something is not enough, in that case, we are going to cover the other 3 methods.
2. Apostrophe Method
This is the second simple method to convert numbers to text in excel. This method does not require any function or formula. It also prevents your leading zero change automatically.
All you need is to Add an Apostrophe before your number.
Example: '234
Note: You can use these numbers for your calculations, it won’t affect any arithmetical or logical operation.
It will not only prevent your leading zero from vanishing but also prevent converting “02-10” like numbers into the date also. All you need is to do the same thing that you have already done above.
Example: '02-10
However, this method is useful only when you manually enter your data. In case you are already having a large amount of data then this method consumes your precious time. Don’t worry, if you have a large date then go for the Cell Formatting method by selecting a specific range.
3. Columns Wizards Method
This is also one of the easiest methods to deal with this problem. I often prefer this method whenever I deal with a large number of values or when my data is too large.
- The first step is to select the column that you want to convert to text. Click On the “DATA Tab”. Where you can find the option “Text To Columns”.
- Column wizard will pop up. Click the next button until you reached the final page of Column Wizard. Here you have to select “Text” as shown in the below image. After that click on Finish.
- As soon as you click on Finish, you will see that all your numbers are converted to text in excel.
You might have thought about the green triangle that appears on each cell. Let me answer your question. It is nothing but the way excel tells that there are numbers stored in text, isn’t it cool?
4. TEXT Function Method
The TEXT Function Method is the most complicated method of all that is the reason I save it for last. Here we are using Built-In Excel Text Function. Text Function allows us to convert numbers to text but it requires an additional format that should be given by us.
You can also use 0.00 as the second argument if you require your numbers in decimal points. It also deals with currency date and time format. TEXT Function is something that you should play with if you want to know more about it.
So, here we end our tutorial on “convert numbers to text using excel”. Hope it will provide some value and be worth the time that you have spent here today.
