+ Reply to Thread
Results 1 to 7 of 7

Help needed on how to extract substrings from comma delimited text

  1. #1
    Registered User
    Join Date
    05-13-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Help needed on how to extract substrings from comma delimited text

    Hi,

    I have spent hours trying to come up with a solution to the below. I would really appreciate any help.

    I have data in the following format in the A column:

    Backlog, Release 1, Release 1 Iteration 12
    Backlog, Release 1, Release 1 Iteration 13 (First Pass)
    Backlog, Release 1 Iteration 12, Release 1
    Backlog, Release 1 Iteration 14 (Second Pass), Release 1

    What I could like it to read in the text and look for anything with "Iteration" and return the bit between the commas. If iteration is at the end then just return the end bit after the final comma.

    The result would be:
    Release 1 Iteration 12
    Release 1 Iteration 13 (First Pass)
    Release 1 Iteration 12
    Release 1 Iteration 14 (Second Pass)

    I'm not sure if this is possible using standard excel functions since the length of the sub-string is not fixed and can change depending on what it is named as.

    Many thanks in advance.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help needed on how to extract substrings from comma delimited text

    Try this:
    Please Login or Register  to view this content.
    Quang PT

  3. #3
    Registered User
    Join Date
    05-13-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Help needed on how to extract substrings from comma delimited text

    Thanks bebo for the quick reply. That worked great!

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Help needed on how to extract substrings from comma delimited text

    If Your data starts in A1:

    =MID(A1,FIND("CC is really great",SUBSTITUTE(A1,",","CC is really great",LEN(LEFT(A1,SEARCH("iteration",A1)))-LEN(SUBSTITUTE(LEFT(A1,SEARCH("iteration",A1)),",",""))))+2,IFERROR(FIND(",",A1,SEARCH("iteration",A1))-10,255))

    Although, this took me so long it's probably already been solved...

    It has been solved, it's been solved with a better solution. Ho hum.
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help needed on how to extract substrings from comma delimited text

    "Cheeky Charlie is really great"? best effort with non array-formula

  6. #6
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Help needed on how to extract substrings from comma delimited text

    Thanks bebo! Although I don't think your solution is an array formula, it's very elegant in fact!

  7. #7
    Registered User
    Join Date
    05-13-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20
    Thanks for the alternative solution

+ 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