Converting Number To Text in Excel I How To Convert Numbers To Text In Excel
Have you ever come across a situation where you want to convert number to text while working on an Excel sheet and have had to do it manually? You wouldn’t have to do it anymore after reading this article.
In this article, we will learn the art of converting number to text in Excel. Whether it is needed to search parts of numbers within entire numbers or to perform a lookup function on them with the help of wildcards, it is not possible to do that with numeric values. For this, you will have to convert them to text.
Let’s learn how to convert numbers to text in excel!
Using the Excel TEXT function to convert numbers to text
One of the easiest ways to convert numbers to text in Excel is by using the TEXT function. Not only does this function convert your numeric values to a textual format, but it also helps you to choose how you wish to display your decimal points as per your requirements. It is important to note that after converting your numbers to text, you will not be able to perform numeric functions on that data because it gets stored in Excel as text.
Follow these steps to use the TEXT function:
- Consider the following data. Type the formula =TEXT(A2,”0”) in the desired column where you need the number to be converted to text. A2 is the cell that contains the respective numeric value.
- Copy the formula for the rest of the cells by dragging it.
- After this, you will see that all the numbers will change to text format and will be left aligned. This means that now they are stored on your sheet as text and not numbers.
- Now it’s time to convert formulas to values. Select the entire column B.
- Use CTRL+C to copy the entire column. After that, use CTRL+ALT+V to open the Paste Special dialogue box. Under the “Paste” group, select the values button.
- This will make a small triangle appear on the top right of all cells, indicating that the numbers have been converted to text.
Here’s a list of some variations of TEXT function:
- =TEXT(631.725,”0”) will result in 631
- =TEXT(631.672,”0.0”) will result in 631.7
- =TEXT(631.652,”0.00”) will result in 631.65
Using the FORMAT CELLS function to convert number to text
- Select the cells with numeric values that you want to format into text.
- Post selection, use right-click to choose FORMAT CELLS from the menu.
- From the FORMAT CELLS dialogue box that appears, select Text option from the Number Tab and press OK.
- This will convert the numbers to text format. That’s how you can format Text in Excel.
By using VBA or creating a macro, you can also build a number to text converter in Excel for yourself in Excel.
Using the apostrophe to convert number to text
It may not be the most recommended way, but if the number of cells with numbers that require conversion is limited, then you can also use this method. All you have to do is double-click on individual cells and add the apostrophe(‘) sign before the number.
A small triangle will appear on the top corner of the cell, indicating that the number of cells has been formatted to text.
Using the Text To Columns to convert number to text
Even though it is highly uncommon, you can also use the Text To Columns feature and convert your numeric data to text.
- Select the column on the Excel sheet from which you want to convert Excel number to string.
- From the Data tab, select the Text To Columns option.
- After going through the first 2 steps of the wizard, select the Text radio button on the third step and select Finish.
- It’s done, your numbers are now converted to text.
These were all the ways you can convert numbers to text in Excel. It would be our recommendation to go through the Text to Columns and Excel TEXT Function in depth to understand and use them in a better manner.
We hope this article provided you with all the information you were looking for on converting number to text in Excel. We also hope it provided you with an insight into how to build your own number to text converter in Excel.
Feel free to drop your /questions/comments/suggestions/feedback about this article on “How to convert numbers to text in Excel” in the comments section and our team would be happy to address your concerns.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Doubt Resolution