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.

  1. 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.
    Convert number to text in Excel
  2. Drag the formula to rest of the cells.
    Convert number to text in Excel
  3. 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
    Convert number to text in Excel
  4. Now we need to convert formulas in cells to values. Select column B.
  5. 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.
    Convert number to text in Excel
  6. 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:

  1. =TEXT(543.652,”0”) will result in 543
  2. =TEXT(543.652,”0.0”) will result in 543.7
  3. =TEXT(543.652,”0.00”) will result in 543.65

Converting number to text in excel using Format Cells option

  1. Select the range of cells which you want to format as text.
  2. Right click and select Format Cells option from menu list.
    Convert number to text in Excel
  3. Format Cells dialog box will appear. Under Number tab select Text and hit OK.
    Convert number to text in Excel
  4. The numbers are now formatted as text.
    Convert number to text in Excel

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.

Convert number to text in Excel

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.

  1. Select the column where you want to convert numbers to string in excel.
  2. Go to Data tab and select Text to Columns.
    Convert number to text in Excel
  3. Just go through first 2 steps of the wizard. On third step, select Text radio button and hit Finish.
    Convert number to text in Excel
  4. The numbers are converted into text.

Conclusion

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.

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Ask a Question or Leave a Reply

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Learn Army Logo
Learnarmy.com Copyright © 2020 All Rights Reserved