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

Thread: Extract text that has a delimiter

  1. #1
    New Member
    Join Date
    Mar 2008
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract text that has a delimiter

    I know this can be done using text to columns, however, my situation requires that I do this with a formula in a cell next to my text string (sorry, no VB code).

    Constants:
    1- There are always 6 values separated by 5 semi-colons
    2- None of the "good" data will have semi-colons (the only time a semi-colon is found in the text string because it's my delimiter
    3- The "good" data between the semi-colons can vary in length

    Here are 2 samples of the data:
    123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
    222.10.246.30;255.255.0.0;cnn.com;DEF v2.30.40;17C2D52;Status=Failed

    For my formula, I want to extract out the 4th value (which is "ABC v1.2.3" for record 1 and "DEF v2.30.40" for record 2.

    Thanks in advance!

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    Not sure if I follow this 100%:
    2- None of the "good" data will have semi-colons (the only time a semi-colon is found in the text string because it's my delimiter

    In your example the "good" data had a semi-colon attached at the end of it? Will the good data always be found starting at the 3rd semi-colon?

  3. #3
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,642
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    Excel Jeanie HTML ******>



    Sheet3

     A
    1 
    2 
    3 
    4 
    5 
    6123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
    7ABC v1.2.3
    8 
    9 

    Spreadsheet Formulas
    CellFormula
    A7=MID(MID(MID(SUBSTITUTE(A6,";","^",3),1,256),FIND("^",SUBSTITUTE(A6,";","^",3)),256),2,FIND(";",MID(MID(SUBSTITUTE(A6,";","^",3),1,256),FIND("^",SUBSTITUTE(A6,";","^",3)),256))-2)


    Excel tables to the web >> Excel Jeanie HTML 4






  4. #4
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,528
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    Why not just...

    Sheet1

     A
    1123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
    2ABC v1.2.3

    Spreadsheet Formulas
    CellFormula
    A2=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3))-1)


    Excel tables to the web >> Excel Jeanie HTML 4

  5. #5
    New Member
    Join Date
    Mar 2008
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    iliace - thanks! that's exactly what I needed. iggydarsa - looks like your formula would work find as well.

    iliace - what does the "201" in (CHAR(201) stand for? Is this the system code for a semi-colon or something?

  6. #6
    New Member
    Join Date
    Mar 2008
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    iliace - one more quick question. Assume that my data stays the same. Do I use the same formula if I wanted to extract the 1st, 2nd, 3rd, 5th, or 6th value/field? I'm seeing how this works and I can get it to work for 2nd through 6th by changing the values 3 & 4 in the formula you provided, but I don't see how I could modify this formula to pull the 1st field. Sorry, this is just more for my understanding in case I have the need for it in the future. Thanks again!

  7. #7
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,528
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    Using my approach:

    Sheet1

     AB
    1123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good123.1.135.2
    2 255.255.0.0
    3 yahoo.com
    4 ABC v1.2.3
    5 16A15B14
    6 Status=Good

    Spreadsheet Formulas
    CellFormula
    B1=LEFT(A1, FIND(";", A1)-1)
    B2=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),1)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),1))-1)
    B3=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2))-1)
    B4=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3))-1)
    B5=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),5))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4))-1)
    B6=RIGHT(A1,LEN(A1)-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by iliace; Mar 6th, 2008 at 11:18 PM.

  8. #8
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,528
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    In a single 6-row array formula (with simplified inputs) - uniform formula, and only marginally slower:

    Sheet1

     ABC
    1123; 255; yahoo; ABC;16A; Good123123
    2  255 255
    3  yahoo yahoo
    4  ABC ABC
    5 16A16A
    6  Good Good

    Spreadsheet Formulas
    CellFormula
    B1=LEFT(A1, FIND(";", A1)-1)
    C1{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    B2=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),1)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),1))-1)
    C2{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    B3=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),2))-1)
    C3{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    B4=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),3))-1)
    C4{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    B5=MID(A1,1+FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4)),FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),5))-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),4))-1)
    C5{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    B6=RIGHT(A1,LEN(A1)-FIND(CHAR(201),SUBSTITUTE(A1,";",CHAR(201),LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))
    C6{=MID(";"&A1&";",FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))+1,FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($2:$7)))-FIND("`",SUBSTITUTE(";"&A1&";",";","`",ROW($1:$6)))-1)}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  9. #9
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,528
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text that has a delimiter

    CHAR(201) is an extended character that's unlikely to be in your data. I picked that number arbitrarily.

  10. #10
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text that has a delimiter

    Hi

    Assuming your text in A1 then you can use the following formula:

    =TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("@",SUBSTITUTE(";"&A1,";","@",4))-1,""),";",REPT(" ",100),1),100))

    Amend the red 4 to whatever position you want ie 1 will return 123.1.135.2 and 2 will return 255.255.0.0 in your first stated example etc. If the @ character may appear in your text you can replace that with another (or a multiple occurence which would make it more robust).
    Last edited by Richard Schollar; Mar 7th, 2008 at 03:21 AM.
    Richard Schollar

    Using xl2013

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
  •