Thread: sum cells with text number text

I am creating a spreadsheet for payroll that would be huge without the use of text and numerical values together. The purpose of the spreadsheet is to track overtime hours and calculate pay variations. Each cell indicates one of 11 variables for the 'type' of overtime worked (i.e. Reg OT, Stat holiday OT, Acting Captain OT etc.) then the actual number of hours worked, then *sometimes* a code for the pay period. At its most complicated, a cell could look like this:

APCSTAT 10 LB4 (Acting PC on a STAT for 10 hours submitted late by 4 weeks)

or as simple as:

OTSTAT 6

I can have 11 different columns for each type of overtime, but I need a formula to add up the hours for each code, as well as a way to check for the 'late by' code and add it if necessary....help please anyone?

***UPDATE***

So I have come up with this formula which works for summing the hours worked:

=SUMPRODUCT(--(TRIM(REPLACE($C$5:$I$5,1,SEARCH(" ",$C$5:$I$5&" "),""))="AC"),--LEFT(0&$C$5:$I$5,SEARCH(" ",$C$5:$I$5&" ")))*5.39

but I still need to figure out a formula to catch late entries (cells containing LB#) since the above formula does not capture them at all...
Since this is a new process- I am open to ideas to better track this information, bearing in mind there are aprox. 200 employees and this is a 24/7 operation, so there will be a lot of information
dummy workbook attached - thanks!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!