Help Converting Partial Text, Partial Number Cells to Number Only

I'm trying to utilize a spreadsheet which our production analysts compile regarding actual hours worked of production staff. There are hundreds of rows and 20-30 columns of data, all in one of the following formats:

#A #

A #

#

where 'A' represents a text letter (either 'a', or 'b') signifying "After" or "Before". They utilize this format to track the regular hours worked (first number) and overtime hours worked (the number following the text character). For example:

12a 6 = 12 regular hours, and 6 overtime hours after their regular shift

b 7 = 7 overtime hours before their regular shift

24 = 24 regular hours

I need to convert these cells into only numbers. I'm not interested in breaking out overtime. For example, I need to see:

12a 6 = 18

b 7 = 7

24 = 24

Is there an easy way that I can swap these out? This is a monthly exercise which I will need to do, and it is so time consuming doing one at a time! (or even several at a time if I do a find, replace).

Thanks!

It would be best if you uploaded a sample workbook that contains all of the variations so that a complete solution can be developed. Be sure to remove any sensitive data before uploading.

Here's one way, if VBA is acceptable. Select the cells of interest and run this:

Please Login or Register  to view this content.

A shot in the dark:

Please Login or Register  to view this content.

You didn't identify which version you use, so...

Cheers,

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

Running a Macro
1. Do Alt+F8 to open the macro dialog
2. Select the macro name from the dropdown list and press Run

So don't select any blank cells.

Please Login or Register  to view this content.

Please Login or Register  to view this content.