How to convert numbers to text in excel
In this article we’ll learn how to convert number to text in excel using Excel TEXT Function, Text to Columns and by formatting text in excel.
There might be situations when you would want to convert excel number to text. If you want to perform a Lookup on numbers using wildcards then it can’t be done on numeric values. Or if you want to search certain part within the entire numbers like find numbers which have 12 anywhere in them. For this you need them as text values.
Let’s see how converting number to text in excel takes place.
Convert number to text using Excel TEXT function
This is one of the easiest ways. This functions turns the given number into text and also allows you to still display decimal points as you wish. Do remember the TEXT function make the number stored as text in excel so you can’t perform numeric functions on that converted text.
- We have the following data set as below. Type the formula =TEXT(A2,”0”) A2 is the cell which contains the numeric value which we want to convert.
- Drag the formula to rest of the cells.
- All the numbers have been converted and you can see they are left aligned now meaning excel is now treating values in cells like text
- Now we need to convert formulas in cells to values. Select column B.
- Press CTRL + C to copy the column. Then press CTRL + ALT + V which will open Paste Special dialog box. Select the Values radio button under Paste group.
- You’ll see a small triangle on top right of all cells, this implies that excel has converted number as text.
Let’s look at the formula again. =TEXT(543.652,”0”) the second parameter shows how the number will look after being converted. Let’s see few examples:
- =TEXT(543.652,”0”) will result in 543
- =TEXT(543.652,”0.0”) will result in 543.7
- =TEXT(543.652,”0.00”) will result in 543.65
Converting number to text in excel using Format Cells option
- Select the range of cells which you want to format as text.
- Right click and select Format Cells option from menu list.
- Format Cells dialog box will appear. Under Number tab select Text and hit OK.
- The numbers are now formatted as text.
You can also build a number to text converter in excel. By either creating a macro or using VBA.
Use apostrophe to change number to text format
This is not the best way but if you only have a few cells where you want to convert excel number to string then use this. Just double click the cell and add an apostrophe before the numeric value.
You’ll see a small triangle in top corner meaning excel text formatting has been done and these numbers are now text values.
Convert numbers to text using Text to Columns in Excel
It’s generally not used but you can use Text to Columns feature to convert numbers to text.
- Select the column where you want to convert numbers to string in excel.
- Go to Data tab and select Text to Columns.
- Just go through first 2 steps of the wizard. On third step, select Text radio button and hit Finish.
- The numbers are converted into text.
That was all about converting numbers to text in excel. I recommend go through Excel TEXT Function and Text to Columns to better understand how to use both.
I hope this article was helpful to you and explained you in excel how to convert number to text and how to build number to text converter in excel.
If you have any questions or tips, I’d love to hear them in the comment section below.