The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

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).

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).

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

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

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:

Excel Text to Number Conversion