+ Reply to Thread
Results 1 to 13 of 13

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

  1. 06-15-2015, 03:47 PM #1
    syncguy
    syncguy is offline
    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
    Reply With QuoteRegister To Reply
  2. 06-15-2015, 03:51 PM #2
    syncguy
    syncguy is offline
    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
    Reply With QuoteRegister To Reply
  3. 06-15-2015, 03:54 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    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
    Reply With QuoteRegister To Reply
  4. 06-15-2015, 04:06 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  5. 06-15-2015, 04:36 PM #5
    syncguy
    syncguy is offline
    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
    Reply With QuoteRegister To Reply
  6. 06-15-2015, 05:09 PM #6
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    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
    Reply With QuoteRegister To Reply
  7. 06-15-2015, 05:13 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    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)
    Reply With QuoteRegister To Reply
  8. 06-15-2015, 05:15 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

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

    Willem, where is that Fliptext coming from?
    Reply With QuoteRegister To Reply
  9. 06-15-2015, 05:23 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert 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

    Duplicate thread:

    https://www.excelforum.com/showthread.php?t=1088320
    Reply With QuoteRegister To Reply
  10. 06-15-2015, 05:35 PM #10
    syncguy
    syncguy is offline
    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 !
    Reply With QuoteRegister To Reply
  11. 06-15-2015, 05:40 PM #11
    syncguy
    syncguy is offline
    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
    Reply With QuoteRegister To Reply
  12. 06-15-2015, 05:42 PM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  13. 06-15-2015, 05:52 PM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

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

    Thanks for the feedback

    (I have merged the otehr 1 into here)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. How to count occurrence of items occurring both singly or in a string within a cell
    By Supersadie in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1