Page 1 of 6 123 ... LastLast
Results 1 to 10 of 54
Like Tree3Likes

Extract A String Between Two Characters

This is a discussion on Extract A String Between Two Characters within the Excel Questions forums, part of the Question Forums category; I'm stuck. I need a formula to extract data from between two characters. For Example, In A1 I have this: ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    27

    Question Extract A String Between Two Characters

    I'm stuck. I need a formula to extract data from between two characters.

    For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

    I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

  2. #2
    Board Regular 6StringJazzer's Avatar
    Join Date
    Jan 2010
    Location
    Vienna, VA, USA
    Posts
    67

    Default Re: Extract A String Between Two Characters

    I don't want to scare you but here is the formula, if you insist on doing it in a single formula. This is a mega-formula that I built up from several smaller formulas. It will work for any lengths. It is very difficult to read and maintain.

    =LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))),FIND("_",RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))))-1)

    If you put your value in A1, you can build up to it using the following formulas in the cells indicated:

    A2 =RIGHT(A1,LEN(A1)-FIND("_",A1))
    A3 =RIGHT(A2,LEN(A2)-FIND("_",A2))
    A4 =RIGHT(A3,LEN(A3)-FIND("_",A3))
    A5 =LEFT(A4,FIND("_",A4)-1)

    Note that each formula needs to do a find plus know the length of the result of the previous step. That's why the mega-formula is so big, because each formula replaces the previous formula twice, and you repeat the replacement process 3 times.

    There might be a slightly simpler way to do this, if it comes to me I'll post again.

  3. #3
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Massachusetts, USA
    Posts
    1,767

    Default Re: Extract A String Between Two Characters

    With
    A1: COMP_PROG_v1_ABCD_01

    This formula returns the characters between the 3rd and 4th underscores (_):
    Code:
     
    B1: =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,
    LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
    In the above example, the formula returns: ABCD

    Does that help?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (Oct 2006 - Sep 2015)
    Using: Excel 2010 & 2013

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    10,114

    Default Re: Extract A String Between Two Characters

    Hi and welcome to the Board
    Have you given any thought to using TEXT to COLUMNS and using your "_" as the delimiter.

    Regards
    Michael M
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular 6StringJazzer's Avatar
    Join Date
    Jan 2010
    Location
    Vienna, VA, USA
    Posts
    67

    Red face Re: Extract A String Between Two Characters

    Quote Originally Posted by Ron Coderre View Post
    With
    A1: COMP_PROG_v1_ABCD_01

    This formula returns the characters between the 3rd and 4th underscores (_):
    Oh crap, ignore mine!

  6. #6
    New Member
    Join Date
    Jan 2010
    Posts
    27

    Default Re: Extract A String Between Two Characters

    Thanks Guys. Ron, yours does exactly what I want it to. Thanks

  7. #7
    New Member
    Join Date
    May 2012
    Posts
    1

    Default Re: Extract A String Between Two Characters

    I've read a bunch of things here on line but can't seem to figure out my own equation. I'm also trying to extract data between two areas of an URL.

    Here are some examples of the portions of URLS I'm working with:

    CFID=323428940&CFTOKEN
    CFID=28769009&CFTOKEN
    CFID=8000597&CFTOKEN

    I want to extract the numbers between "CFID=" and "&CFTOKEN" but as you can see they can change from 7-9 characters each.

    Does anyone have any ideas?

    Thanks!

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    27

    Default Re: Extract A String Between Two Characters

    Yours would be a little different.

    You could try this:

    PHP Code:
    =MID(YourTextHere,6,LEN(YourTextHere)-13

  9. #9
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: Extract A String Between Two Characters

    You could just use =SUBSTITUTE(SUBSTITUTE(A1,"CFID=",""),"&CFTOKEN","")
    "I'm not a perfectionist; I'm just perfect."

  10. #10
    New Member
    Join Date
    Apr 2013
    Posts
    2

    Default Re: Extract A String Between Two Characters

    I have a similar question.

    blahblahtext moretext, Result1: 454654654, Result Number2: ABC, Result Number3: 445344

    I have the above string. I don't care about the actual results, but rather the headings. I want what is between ", " and ":" in one cell, what's between the next ", " and ":" in the next cell., etc etc

    Results should look like this:
    Cell1: Results 1, Cell2: Result Number2, Cell3: Results Number 3

    I tried modifying Ron's formula above but just confused myself.

Page 1 of 6 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com