+ Reply to Thread
Results 1 to 13 of 13

Extract data appearing after 4th occurrence consistently re-occurring character

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Question Extract data appearing after 4th occurrence consistently re-occurring character

    Hi , this one is tricky... Any help would be appreciated. Could use a VBA if that would be easier than a formula. I'm trying to just extract the NAMES from the data, which always appears after the 4th occurrence of the "-" character. Example below. Thanks

    Screen Shot 2015-06-15 at 3.42.29 PM.png

  2. #2
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Question Extract data appearing after 4th occurrence consistently re-occurring character

    Hi , this one is tricky... Any help would be appreciated. Could use a VBA if that would be easier than a formula..

    I'm trying to just extract the NAMES from the data, which always appears after the 4th occurrence of the "-" character. Example below. Thanks
    Attached Images Attached Images

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Try this...

    Data Range
    A
    B
    1
    data - data - xxx - yyy - Joe, Pittsburgh
    Joe
    2
    blah - doh - a - b - Jean, Beaver Falls
    Jean
    3
    o - u - q - t - Patty, New Brighton
    Patty
    4
    1 - more - sample - here - Scoobie Doo, Allison Park
    Scoobie Doo


    This formula entered in B1 and copied down:

    =REPLACE(LEFT(A1,FIND(",",A1)-1),1,FIND("^",SUBSTITUTE(A1,"-","^",4))+1,"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    hi HDibbins , whoops , sorry! here you go . Sample worksheet attached. Thanks !

    _forum example_.xls

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,983

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Take a look at these 2 formulas
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Try this in C2, copied down...
    =LEFT(MID(A2,FIND("xx",SUBSTITUTE(A2,"-","xx",4),1)+2,99),FIND(",",MID(A2,FIND("xx",SUBSTITUTE(A2,"-","xx",4),1)+2,99),1)-1)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Willem, where is that Fliptext coming from?

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract data appearing after 4th occurrence consistently re-occurring character


  10. #10
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Thanks much you guys,

    Willem's work well , as did your's FDibbins. Great stuff.. thank you !

  11. #11
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Thanks Tony , sorry or the oversight on this duplicate post. Don't know how I did that..
    thanks

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    No problem. The forum acts up now and then. I had a duplicate post earlier today.

    A moderator may see this and merge the two threads together.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Extract data appearing after 4th occurrence consistently re-occurring character

    Thanks for the feedback

    (I have merged the otehr 1 into here)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Getting the string that follows the last occurrence of a character
    By michaljireht in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2014, 10:25 AM
  2. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  3. [SOLVED] Extract Data from one sheet another without blanks appearing
    By suraiya in forum Excel General
    Replies: 3
    Last Post: 12-24-2013, 07:10 AM
  4. Update Values dialog box not appearing consistently
    By myorns in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-06-2011, 10:38 AM
  5. Replies: 5
    Last Post: 08-23-2010, 07:05 AM

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