Excel Worksheet Tips: convert numbers to text and text to numbers

By

Excel Worksheet Tips: convert numbers to text and text to numbers

I use excel on a daily basis and as I come across things that I have needed to look up, I will post them here. The first thing I have come across is converting numbers stored as text into actual numbers and vice versa.

Converting numbers stored as text into numbers
When you copy data from a webpage, there are times when the data includes both a selection of numbers and text. Unfortunately, most times, these numbers are stored as text. The easiest way to convert a large amount of cells to numbers is outlined below.

When you have numbers stored as text, there will be a green arrowhead in the top left corner of the cell.

To remove this and work with the numbers stored in those cells, the quickest way to do this is:

1. Place the number 1 in any blank cell.

2. Copy this number one, and then select all the cells that are numbers stored as text. Right click on the selected cells and select paste special.

3. Ensure you select “multiply”. Press ok once selected.

4. That’s it! All your numbers should now be real numbers.

Converting numbers into text
Now, I’m not sure why you would want to do this, but just to show both ways….
1. Assuming that your number is in cell A1, enter the following code into a blank cell: =TEXT(A1,”0″)

2. Once you have done this for all the cells you wish, copy the cells with the code in them and once again, right-click where you want to paste the values and select “paste special”. Select “values only” and your values will be displayed as text as per below.

That’s all there is to it. Hopefully you can use this to save you time.

Come check out the directory for the rest of my excel tips!

Submeg

IT

Advertisements