?
Solved

How to Convert Alpha to Numeric in Excel

Posted on 2010-01-11
8
Medium Priority
?
1,490 Views
Last Modified: 2013-12-05
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!
0
Comment
Question by:Delivery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26290569
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26290581
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26290599
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
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26291312
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.
0
 

Author Comment

by:Delivery
ID: 26300049
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?
0
 

Author Comment

by:Delivery
ID: 26300123
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.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26300169
See if this macro works any faster.

Public Sub ConvertTextToNumber()

    Dim Cell As Range
    Dim Calculation As Long
   
    Application.ScreenUpdating = False
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each Cell In ActiveSheet.UsedRange
        Cell.Value = Cell.Value
    Next Cell
    Application.Calculation = Calculation
    Application.ScreenUpdating = True

End Sub

Kevin
0
 

Author Closing Comment

by:Delivery
ID: 31675945
Zorvek,

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

Thanks

Steve
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question