+ Reply to Thread
Results 1 to 7 of 7

Nested formula to find the next instances of the same word in a single cell string

  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Angry Nested formula to find the next instances of the same word in a single cell string

    Hello,
    i've been stumped to figure out my nested formula for the next instance of the same instance in a single cell,
    here is my formula MID(AL2,SEARCH(“Oper”,AL2,1)+6,3) can someone please help me with the nested formula to find the next instance of the exact same thing? I know for the pros this is easy but i'm still trying to understand the way it works and would really appreciate someone's help to solve this problem i've been working on for a week.....I need the Oper ID's and the dates only but how????

    the text looks like this:
    "Oper: YW Date: 12/14/12 9:39am
    mortgagee cls to sign, e-amiled to debbie for signature, also
    just another correction april

    Oper: KB Date: 01/21/13 11:55am
    emailed for signature again.

    Oper: KB Date: 01/22/13 10:38am
    rcvd signed - emailed to company through website for processing."

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Nested formula to find the next instances of the same word in a single cell string

    hi there. assuming your settings is m/d/y since you are in US, and you just need the date & not the time, try:
    =MID(AK2,SEARCH("date:",AK2)+6,8)+0
    format it to date format.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Nested formula to find the next instances of the same word in a single cell string

    if you are returning the KB Date: 01/21/13 11:55am

    maybe this could help also

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Registered User
    Join Date
    01-30-2013
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Nested formula to find the next instances of the same word in a single cell string

    Hi Benishiryo,
    thank you for responding first of all i really appreciate that. for the formula i'm working some have strings of anywhere from 1-15 and i need to separate out not just the dates individually but the diffrent OPER: "ID's" up to 15 times in 15 diffrent rows. Plus to make it worst the spaces and the lengths all vary. This is what's making it so hard for me to figure out. Below i will show a quick look at the way the data should show in the colunms and then you can use your expertise to help me out please.


    Oper ID 1 // Oper ID 2// Date 1 // Date2
    YW // KB // 12/14/12 // 1/21/13 ..... and so forth up to 15 instantces




    Oper: YW Date: 12/14/12 9:39am
    mortgagee cls to sign, e-amiled to debbie for signature, also
    just another correction april

    Oper: KB Date: 01/21/13 11:55am
    emailed for signature again.

    Oper: KB Date: 01/22/13 10:38am
    rcvd signed - emailed to company through website for processing
    Last edited by Rapper2nite; 02-06-2013 at 12:14 PM. Reason: Post doesn't look the way i posted for my example on how I need this formula to work for me.

  5. #5
    Registered User
    Join Date
    01-30-2013
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Nested formula to find the next instances of the same word in a single cell string

    Hi Vlady,
    thank you as well for responding I really appreciate that. for the formula i'm working some have strings of anywhere from 1-15 and i need to separate out not just the dates individually but the diffrent OPER: "ID's" up to 15 times in 15 diffrent rows. Plus to make it worst the spaces and the lengths all vary. This is what's making it so hard for me to figure out. Below i will show a quick look at the way the data should show in the colunms and then you can use your expertise to help me out please.


    Oper ID 1 // Oper ID 2// Date 1 // Date2
    YW // KB // 12/14/12 // 1/21/13 ..... and so forth up to 15 instantces




    Oper: YW Date: 12/14/12 9:39am
    mortgagee cls to sign, e-amiled to debbie for signature, also
    just another correction april

    Oper: KB Date: 01/21/13 11:55am
    emailed for signature again.

    Oper: KB Date: 01/22/13 10:38am
    rcvd signed - emailed to company through website for processing
    Last edited by Rapper2nite; 02-06-2013 at 12:19 PM.

  6. #6
    Registered User
    Join Date
    01-30-2013
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Nested formula to find the next instances of the same word in a single cell string

    Vlady I just tried your formula again and it works nicely but how do I nest it up to 15 times, i've been using excel and understand Pivot tables, vlook ups however not too familar with the nested formulas can you help me one step further please?

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Nested formula to find the next instances of the same word in a single cell string

    can you provide a real workbook for that so we can see the layout of the data and also put those expected results in an expected manner.

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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