Link to home
Start Free TrialLog in
Avatar of Delivery
Delivery

asked on

How to Convert Alpha to Numeric in Excel

I am running a sql query in Excel against an Access database so I that can perform analysis on the results.

But, 1st time i run it I realize the data is all coming back as text not numbers.  Turns out the programmer found it easiest to write in access this way.

Once i extract the data, how can I easily convert it to numeric?

I know that if I select the alpha data carefully, an info box will appears that allows me to select "Convert to numbers".  

But I need to convert automatically-maybe with a macro.  So careful selection is out.  Data will always be different shapes and sizes.

Is there a command in Excell that will apply this powerful conversion function at will to a range of numbers?  I can only find it when I select a range that both starts and ends with an alpha that Excel thinks might be a number.  Off by one cell and the info box never pops up.

I am going crazy!
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Sometimes numbers, dates, and times can be entered into worksheet as text values which prevents them from being formatted and sorted. This frequently happens when data is imported from an external source such as a database. To convert such values to real numbers, dates, and/or times, select any unused empty cell and choose the Edit->Copy menu command. Select the cells containing the text version of the numbers or dates to be converted. Choose the menu command Edit->Paste Special. Select the Values radio button in the Paste section, the Add radio button in the Operation section, and click OK. Note that this technique also can be used to remove single quotes from in front of numeric data.

If any dates show as numbers then select those cells and choose the menu command Format->Cells, navigate to the Number tab, select Date in left list and the desired date format from the right list. Click OK.

If all dates show as numbers but display as dates in the formula bar, choose the menu command Tools->Options, navigate to the View tab, and uncheck Formulas in the "Window options" section.

If using Excel 2003 then choose the menu command Tools->Options, navigate to the Error Checking tab, check on "Number stored as text", and click OK. Any numeric values stored as text now have a small green triangle in the upper left corner of the cells. Select the afflicted cells, click on the warning icon that appears next to the selection, and choose "Convert to Number" from the menu that appears. If the warning icon is not displayed or the "Convert to Number" is not available then select a smaller set of cells. Another technique is to select the first cell with the green triangle, move the mouse over the alert popup to activate it, while pressing the SHIFT key, use the arrow keys to select the other cells, and then choose the corrective action from the popup control menu.

If converting a date or time and the above technique does not work, then the text has to be broken into parts to be properly converted. To do this four functions are required: LEFT, MID, RIGHT, and VALUE. The LEFT, MID, and RIGHT functions pulls parts of a string so they can be assembled in a way that Excel can interpret. The VALUE function interprets the assembled parts into a date time value. For example, assuming the text string in cell A1 is formatted as:

   Feb-14-07 18:20:11

Excel does not recognize this as a date value and so the parts must be extracted and reassembled in order to convert the string into a date time value:

   =VALUE(MID(A1,5,2)&LEFT(A1,3)&MID(A1,8,2))+VALUE(RIGHT(A1,9))

The above formula takes the various date and time parts and assembles them into two strings: a date string and a time string, each in a form that Excel can interpret. More specifically:

   =VALUE("14"&"Feb"&"07")+VALUE("18:20:11")
   =VALUE("14 Feb 07")+VALUE("18:20:11")
   =2/14/2007 6:20:11 PM

Different date formats have to be handled with different rules but the technique is always basically the same: reassembling the parts in way that Excel can interpret them. The three functions used to pull the parts from the string are described below.

   LEFT(string, length) - Returns the specified number of characters (length) from the left side of the string.

   RIGHT(string, length) - Returns the specified number of characters (length) from the right side of the string.

   MID(string, start, length) - Returns the specific number of characters (length) from the string starting at the specified location (start).

When assembling the parts, the assembled string must be a recognizable date format. Below are some examples of date formats that Excel recognizes as dates.

   8/22/2008
   8/22
   22-AUG-2008
   2008/02/23
   5-JUL

Note that the day and month might be reversed in some location such as the United Kingdom. Also note that the date separator character may be different. Check the Regional Settings control panel to determine the day month order and the date separator character.

If the text form of the date contains the character form of the month and the regional settings are set for some other language than the date is in, then Excel will not recognize the month text and the month must be translated to a month number. The formula below illustrates how to do this using an English language date and assuming the date is formatted as "Dec 25, 2007" and is stored in cell A1.

   =VALUE(RIGHT(A1,4)&"/"&MATCH(LEFT(A1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)&"/"&MID(A1,5,2))

Kevin
Here is a macro that will convert the selected cells:

Public Sub ConvertTextToNumber()

    Dim Cell As Range
   
    For Each Cell In Selection
        Cell.Value = Cell.Value
    Next Cell

End Sub

Kevin
Note that the above macro is harmless with numbers already stored as numbers so you can select the entire used range and run it.

Or use this macro on the active worksheet:

Public Sub ConvertTextToNumber()

    Dim Cell As Range
   
    For Each Cell In ActiveSheet.UsedRange
        Cell.Value = Cell.Value
    Next Cell

End Sub
You shouldn't really need a loop - using:
Selection.Value = Selection.Value
should do it, unless that data has come across with leading apostrophes, as is sometimes the case with Access.
Avatar of Delivery
Delivery

ASKER

Great suggestions.  They all seem to work.

My favorite is the copy/paste using values/add radio buttons because this is a very large spreadsheet with number fields (in text format) sprinkled throughout actual text fields. Plus it seems almost instantaneous.   I would love to just be able to grab the whole range and copy it to another using this tool.  Good way to maintain data integrity.

Problem:  when I do this, the real text fields are obliterated.  Any way to use this technique, but potec the real text?  I could select only the fields that need converting, but this is tedious.  Plus I need to repeat this conversion daily as new data is created.

I am not too strong on macros, but was able to get the ones suggested to work.  The macro works well and doesn't seem to affect real text fields.  But, just testing them on one column of "numbers" X 250 records took forever.  This file have 25 columns of number sprinkled amongst 40 total columns.  And there are 1000+ records in the file.  I think this approach will take all night.  No?
Zorvek,

Now am trying copy/paste method and paying closer attention.

The values are doubled when I use this approach.  I assume it is because i am choosing Add?

Loe the approach-fast and easy to do.  But, now 2 strikes.  Doubles the value and obliterates real text.

Any way around this?  If i choose None (instead of Add) nothing happens.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Zorvek,

This was sweet.  Worked much faster and handled my entire range without hurting actual tet fields.

Thanks

Steve