formula to return value from string of text if certain criteria exists

Hi
I would like to have formula (can be VBA, or combination of VBA function and formula, but ideally would prefer plain formula) that would take certain value within string of text (from certain location), if conditions are met. Below is the data that I have in col B:

Adam:h_12_07:00-19:00_bla bla bla
Barry:h_0.5_07:30-08:00_bla bla bla
James:h_3_07:00-10:00_bla bla bla
blank
mike:has day off
Steve:h_12_07:00-19:00_bla bla bla

Now here comes the tricky bit. The criteria/condition. Check if cell B is not blank. I would then like the formula to look at the first letter after the first colon (:). If this first letter happens to be letter "h" (not case sensitive) followed by underscore (_) then the condition is met (i.e. the string contains ":h_" as higlighted in red above).

If criteria is not met then the return blank.
If criteria is met then in col C it would return the value between the first and second underscore (highlighted in green above)
If criteria is met then in col D it would return the value between the second and third underscore (highlighted in blue above)


Before

Adam:h_12_07:00-19:00_bla bla bla
Barry:h_0.5_07:30-08:00_bla bla bla
James:h_3_07:00-10:00_bla bla bla
blank
mike:has day off
Steve:h_12_07:00-19:00_bla bla bla

After

Col B ----------------------------------- Col C --------- Col D
Adam:h_12_07:00-19:00_bla bla bla ----- 12 ------------ 07:00-19:00
Barry:h_0.5_07:30-08:00_bla bla bla ----- 0.5 ----------- 07:30-08:00
James:h_3_07:00-10:00_bla bla bla ------ 3 ------------- 07:00-10:00
blank ----------------------------------- blank --------- blank
mike:has day off ------------------------ blank --------- blank
Steve:h_12_07:00-19:00_bla bla bla ----- 12 ------------ 07:00-19:00


I have attached spreadsheet with sheets "before" and "after".
I hope someone can help me to achieve this formula. Any help is much appreciated.

for Column C try using

=IF(ISNUMBER(SEARCH(":h_",B1)),MID(B1,FIND("_",B1)+1,FIND("_",B1,FIND("_",B1)+1)-FIND("_",B1)-1),"")

for Column D try using

=IF(ISNUMBER(SEARCH(":h_",B1)),MID(B1,FIND("_",B1,FIND("_",B1)+1)+1,11),"")

if the length of the string to be output in Column D changes from 11, let me know and i can add the code to find the length of that string too.

here is the 'extended' version

Please Login or Register  to view this content.