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.
Bookmarks