Excel Tips – How to Convert Text to Numbers in Excel 2010

Excel Tips – How to Convert Text to Numbers in Excel 2010

written by Mike Marko.

In this tutorial, we will cover how to both detect text and then convert text to numbers in Excel 2010.

Quick Keyboard Trick to Detect If Cells Are Numeric or Text

A cool trick to detect text in a series of numeric values is using CTRL+SHIFT+# to quickly convert them to dates DD-MMM-YY format.  Only the numeric values will be converted to text, otherwise, they will remain unchanged.

How-Convert-Text-To-Numbers-In-Excel-2010Once you have detected if cells are text, there are some tricks you can use to quickly convert text to numbers.

How to Convert Text to Numbers

There are several ways to create a checkmark or checkbox in an Excel worksheet. Here are a few of them.

You may find that sometimes after you import or copy data from a database or other external source, the numbers in your worksheet are actually stored in cells as text. This can cause problems with calculations and sorting, among other things.

A telltale sign that a number is stored as a text is if the numbers are left-aligned in the column instead of right-aligned.

Error Checking

I’ve always found those small green triangles that sometimes appear in the upper-left corner of cells to be an annoyance and can get in the way of seeing neighboring cell values. These green triangles are actually Error Checking indicators.

Sometimes when numbers are entered into cells that are formatted as Text, these small green triangles appear. Since most times I just ignore them, I didn’t realize that they may contain a simple solution to converting text to numbers.

When you select a cell containing one of these small triangles an error icon appears. Point to the icon and you’ll see a small drop-down arrow. When you click on the arrow, one of the options is Convert to Number. Clicking it converts the numbers formatted as text back to numbers. A simple solution that I’ve always overlooked to convert text to numbers. Now you don’t have to.

Paste Special

Another option for converting text-formatted values to numbers is to multiply each cell by 1 in order to force the conversion to regular numbers. Since you are multiplying the contents of the cells by 1, the numbers still look the same, however, Excel actually replaces the text-based contents of the cell with a numerical equivalent.

To use this technique…

1) In a blank cell type 1 and press Enter;

2) Press CTRL+C to copy the value 1;

3) Select the cells that contain the numbers stored as text that you want to convert;

4) Press CTRL+ALT+V to open the Paste Special dialog;

5) Select the Multiply option and click OK;

6) Go back and delete the contents of the cell where you typed 1 in the first step.

For more Excel tips, check out How to Quickly Edit Formulas and How to Select All Cells Matching a Criteria.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

By the way, please be free to check out our own Facebook Fan Page: Mike Marko Prosperity Tips.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010
2. Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010
3. Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010
4. Your Guide on How to Make Money Blogging

 

Excel Tips - How to Convert Text to Numbers in Excel 2010

Author: Mike Marko
Click here to contact Mike
www.IMConsultantServices.com

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want help marketing online, then check out our services at IM Consultant Services.

Article: Excel Tips – How to Convert Text to Numbers in Excel 2010

Facebook Comments