Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Extract string between second and third forward slashes

  1. #1
    New Member
    Join Date
    Aug 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract string between second and third forward slashes

    I have a text string in cell A2 from a google analytics export that reads as follows:

    /true/t-49/p1072.aspx

    Can anyone tell me how i can extract "t-49" from this string, or any other number of characters that would be in-between the second and third forward slashes?

    Thanks!

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract string between second and third forward slashes

    Modified from here http://www.mrexcel.com/forum/excel-q...ml#post2194931

    Sheet1

     AB
    1StringExtract
    2/true/t-49/p1072.aspxt-49

    Spreadsheet Formulas
    CellFormula
    B2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"/","|",2))+1,LEN(A2)),"/",REPT(" ",LEN(A2))),LEN(A2)))


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    New Member
    Join Date
    Aug 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract string between second and third forward slashes

    Whoa! like magic. Thank you so much!

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract string between second and third forward slashes

    Give this formula a try:

    =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),189,99))

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,025
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract string between second and third forward slashes

    See my mini-blog article here for a general formula for extracting specified fields from delimited text...

    Get Field from Delimited Text String
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Jul 2015
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract string between second and third forward slashes

    What formular would I use to change this to extract everything between the 3rd and 4th?

    Thanks

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,025
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract string between second and third forward slashes

    Quote Originally Posted by Noobs View Post
    What formular would I use to change this to extract everything between the 3rd and 4th?
    Did you read the article I provided a link to in Message #5 yet (it provides a general solution for extracting any "field" from a delimited text string)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract string between second and third forward slashes

    Quote Originally Posted by István Hirsch View Post
    Give this formula a try:

    =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),189,99))
    I see now that my formula in post #4 has a typo. The correct version:

    =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),198,99))

Some videos you may like

User Tag List

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
  •