Excel Text to Number Conversion
If you’re using Excel 2002, you may have noticed a strange little box beside some of your cells. When you click it, you’ll sometimes see a message that says, “Number Stored as Text...” and it asks you if you want it left as text, or stored as a number, etc.
While this is a handy tool that allows you to quickly change from a text-number into a regular number, it doesn’t offer much flexibility in the way of making global changes spanning many rows or columns (you can change contiguous blocks, but not a large block of data containing both text-stored and properly-stored numbers).
The majority of cases that involve numbers stored as text in an Excel sheet is from data that has been imported into Excel from a mainframe system. Many times the communication between the two causes strange data anomalies, text numbers being the most common. Luckily, there is a great trick available to quickly and easily change huge blocks of numbers stored as text, into the more appropriate number stored as a plain old number. We’ll use an example with the dreaded Excel apostrophe (see Figure 1).
In this Figure, we can see that the numbers are stored as text because they are by default aligned to the left (numbers align right unless you’ve manually changed that), and because in the formula bar we can see the evil apostrophe! This is an easy thing to overlook, since the apostrophe does not show up in the cell (it is only a text designation, and not a true character being stored in the cell).
If you choose Find/Replace and try to replace the apostrophe with nothing, Excel will not recognize it, and nothing will happen. So how do we fix this block of numbers quickly?
Well, all it takes is a little Excel trickery!
We need to force Microsoft Excel to understand that these are numbers. There’s no better way to do it other than use the numbers in a calculation. The problem with that is we also don’t want to be sticking numbers all over the place in different rows and columns, etc. This is supposed to be easy, right?
So we’ll multiply the numbers with something. Since we don’t want the numbers to change, we’ll multiply them with the only number that doesn’t change them: the number one.
In an empty cell (B1 in our example), type a 1 and hit enter. Then click on cell B1, and click on Edit, then Copy (cell B1 will be surrounded with a marquee, indicating it’s currently on your clipboard). Now highlight the range we’ll need to change (in this case, A1 through A7). Next, click on Edit, and then “Paste Special” to bring up the Paste Special dialog box (see Figure 2).
The Paste Special dialog box is probably the handiest and dandiest tool in Microsoft Excel. Since the number 1 is on our clipboard, we’ll want to select “Multiply” to multiply the entire range we’ve highlighted (A1 through A7) by 1. This forces Excel to turn these into true numbers instead of text, and leaves those numbers just as they were since any number multiplied by 1 is the same number.
That’s it! You can delete that stray number 1 hanging out in cell B1, and your numbers are now nice and pure with no sign of text around. Oddly enough, this trick also works when you multiply the number 1 against text values that are stored with an apostrophe. Multiply 1 against the word “cheese” with an apostrophe in front of it, and you’ll get plain old “cheese” in return. Make good use of this handy trick, and good luck!
©2014 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: