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
SUBMEG 
You saved my 16hours of work in 3 minutes.
Thank you
LikeLike
No worries, let me know if there are other things you need to do and I can write something up.
Submeg
LikeLike
I was just searching for another trick to do the conversion, but obviously there is not (easy one).
LikeLike
THANKS SO MUCH
LikeLike
Wow, this helps a lot (Converting numbers into text). was very useful to complete my program. many thanks 😉
LikeLike
Thanks so much! The reason someone would want numbers to be stored as text is that some numeric fields need preceding zeros – like a zip code. Unfortunately, when you format the column as text and then put these text/numbers in sometimes it makes the ones with no preceding zeros back into numbers even though the cell is formatted text. Thanks I had over 1,000 of these to do and hitting F2 – Enter for all of them would have taken a long time. Thanks again 🙂
LikeLike