If you are a regular excel user with a large number of data in it, a day may come when you wish to change and convert the cells formatted as a number into text in Excel. In this blog, we would learn multiple ways to convert the numbers into text format. Let us first get an insight into different methods that we are going to unlock in this blog.
Multiple Ways to Convert Number To Text in Excel
In this blog, we would be unlocking four different ways. This includes a formula-based method, but if you are not very fond of using a formula, then there are others as well as listed below-
- Inserting Apostrophe Before the Number
- Convert Numbers Using TEXT Formula
- Using Format Cells Dialog Box
- Convert Numbers To Text Using Text To Column Wizard
Once you convert a numeric value to text, you cannot perform any mathematical operations (like addition, subtraction, etc.) with that numbers anymore. Once converted, the number would lose its number characteristics, and for excel, it is no more a number then.
Finally, let us deep-dive into each of these methods and you can choose the most appropriate one to accomplish your task.
Sample Data To Convert Number Into Text
Below is the list of sample numbers which we are going to use for each of these method.
Download this file and practice it as you move further with this blog. Click Download button.
Adding Apostrophe Before Number
Converting a number into a text format by adding an apostrophe sign before it is the easiest and the quickest way one can think of. Follow the below short steps to achieve this.
Select the cell containing the number and press the F2 key on your keyboard to open the cell in edit mode. Now, place your cursor at the beginning of the number in that cell and finally enter the apostrophe sign and press the ‘Enter’ key on your keyboard.
That’s it, now the number in cell A5 is no more a number, but has converted into text.
Perform the above steps with the number in other cells.
When Not To Use This Method
Although this method is the easiest one, however, it is not the best way when you have tonnes of numbers. It is because, you cannot copy this formating to other cells, but the only option is to individually add an apostrophe before each of the numbers.
Using TEXT Formula To Convert To Text
The excel formula lovers (like me) would prefer to convert numbers into text by using TEXT function in Excel because you can customize the format of the cell as per your preference. Enter the following formula in the cell adjacent to the cell containing number (in our case, it is cell B5) and press Enter-
As a result, the excel would return the converted value in the formula cell. Also, you would see that the converted numbers (to text) would align towards the left.
Finally, use the ‘Fill Handle‘ feature to drag and drop the formula to other cells in column B.
The next step is to paste special the formulas result as pure values. To do so, select and copy (Ctrl + C) cell range B5:B12 and use Ctrl + Alt + V keyboard shortcut to open the ‘Paste Special‘ dialog box. Here, select the radio button ‘Values’ and click OK as demonstrated in the image below:
If you are not so fond of using formula, then you can use any of the below explained methods.
Format Cells To Convert Number To Text
The Format Cells Feature is also the quickest method to convert a number into a text string by just following simple steps.
Convert Number To Text Using Format Cells Feature
- Cell Range Select
Select the cell range containing the numbers (B5:B11).
- ‘Format Cells’ dialog box
Use keyboard shortcut Ctrl + 1 to open the ‘Format Cells’ dialog box on your screen.
- Text Category
Under the tab ‘Number’, choose the category as ‘Text’ and click on the OK button to exit the dialog box.
You are done!! The numbers are now formatted as text. As mentioned earlier, the numbers are now no more a number, it has lost its number characteristics.
Text To Column Wizard To Convert To Text
The last method that we are going to see to convert a number into text in Excel is by using the Text To Column Functionality in Excel. Follow the below steps-
Select the cell range B5:B11 and navigate to the Text To Column Wizard dialog box (Data Tab > Data Tools group > Text To Column option).
In the Text To Column Wizard dialog box, click on the Next button twice to pass through the first and the second step of this wizard. You are now at Step 3 of 3 in the Text to Column Wizard dialog box.
Select the radio button ‘Text‘ as highlighted in the image below and click on ‘Finish’ to immediately convert values in the selected cells to text.
With this, we have reached the end of this blog. Please feel free to comment in the comment section below. Your suggestions/critics would be highly appreciated.