+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    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.
    Attached Files Attached Files
    Last edited by rain4u; 02-29-2012 at 02:21 AM. Reason: typos

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

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

    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.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    Thanks DGagnon! This was quick.
    It works brilliantly.
    Output of col D can change as it comes from report where users type it in. I would expect the users to adhere to the format but typos do happen. Could you amend the code to find its length?


    Thank you very much.

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

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

    here is the 'extended' version

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

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    Absolutely brilliant. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1