Help with math functions on cells with letter and number combinations

Long story short,

My companies system (Jonas) Doesn't export to excel. It does, however export to text and text to columns works well enough. I'm trying to build a dashboard to track my labor times for certain crafts (I'm an industrial PM). Rather than the program exporting an over time and a straight time column...It shoves all of the hours into 1 column and denotes this with an "r" or an "o". I can't further text to column to separate this out as it will mess up the data in other rows that share the same column (WHYYYY can't we export to excel >.<)

I used a formula: =IF(ISNUMBER(SEARCH("O",$E2)),$E2,"")
and: =IF(ISNUMBER(SEARCH("R",$E2)),$E2,"") r

These move the 4.00O and 4.00R into the correct columns for OT and ST. So one problem solved!! YAYYY

Now I'm running into back charged hours. -5R + 6R + -2 R etc etc... I need totals for these numbers. My first inclination is to select the columns and "Ctrl F" find and replace "O" and "R" with " ". HOWEVER not everyone in my office is quite as capable of dealing with multiple tasks... and I would love for this to be something everyone can use as it should shave HOURS off of working change orders. Most of it is auto-populated with Vlookup etc from the Jonas export and has the potential to feed an entire dashboard rather than hand keying multiple spread sheets.

I suck at long story short...

I need a way to do math for those cells that retain the O and R without eliminating them. ORRRR I need a way to auto-eliminate them with a formula so it's a plug an play calculation. ANY help would be absolutely awesome!

Thanks!
-Ci

Please attach sample workbook with specific results. This will help to solve your problem.

Thanks,
XJ

Unfortunately I'll have to wait until I've made a few more posts to upload anything

Not true! When you reply go advanced and manage attachments. Upload your file!

Lol...I should probably join a forum for "How to post on a forum" Thank you so much. Here you go. None of this is crucial or private information btw.

Hello,

I am not sure what you need to calculate but maybe this will help. I separated out the numbers only from column H and made a new sheet showing that.

Let me know if I can be of further help,
XJ