Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Extract Text After second dash?

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Text After second dash?

    I have a sheet with over 20,000 rows of item #'s in Column A of a sheet.

    I am looking to:

    1) Find all items that have 2 dashes (i.e. "-"), then...

    2) For those that have 2 dashes, in a new column, put the contents of the item # that come after the second dash.

    Sample data is below:

    abc-scott-special
    kjh-jkhsakjjkhkjh-SAM
    CB-123456jkh-bob
    EX-1824ABCXYZ-justoneday
    AB-123455
    AB-765432
    AB-NOSECONDDASH
    AB-TWODASHES-testhere


    You'll see some of the above has 2 dashes, and some only have one.

    Any thoughts on how to do this?

    Thanks a million!

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Just to be clear, what exactly do you want the results to be in your example ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,284
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Unless you've over-simplified your example, it seems that you really want the text after the LAST dash. If that's true, using your posted sample data (beginning in cell A1), try this:
    Code:
    B1: =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))
    Copy that formula down through B8.

    Using the sample data these are the results:
    special
    SAM
    bob
    justoneday
    123455
    765432
    NOSECONDDASH
    testhere

    Is that something you can work with?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  4. #4
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Thanks Ron. This is very close however some of the results are wrong. Not all of the item #'s have two dashes, some only have one dash. I am trying to only find those that have 2 dashes, and for those that have two, to pull out the contents of what is after the second dash. Does that make sense? LMK. Thank you.


    Quote Originally Posted by Ron Coderre View Post
    Unless you've over-simplified your example, it seems that you really want the text after the LAST dash. If that's true, using your posted sample data (beginning in cell A1), try this:
    Code:
    B1: =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))
    Copy that formula down through B8.

    Using the sample data these are the results:
    special
    SAM
    bob
    justoneday
    123455
    765432
    NOSECONDDASH
    testhere

    Is that something you can work with?

  5. #5
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Do you want the original cell to be intact (leave the data after the second dash), or do you want it removed from the first cell and placed in the next cell? The first can be done using a formula, the second would require a macro.

  6. #6
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Gerald,

    I am looking to get the following results:


    -special
    -SAM
    -bob
    -justoneday
    BLANK SINCE THERE IS ONLY 1 DASH
    BLANK SINCE THERE IS ONLY 1 DASH
    -testhere

    Quote Originally Posted by Gerald Higgins View Post
    Just to be clear, what exactly do you want the results to be in your example ?

  7. #7
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Perhaps:

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))<=1,"",MID(SUBSTITUTE(A1,"-","^^",2),FIND("^^",SUBSTITUTE(A1,"-","^^",2))+2,LEN(A1)))

  8. #8
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Sure, it can all be done in the same cell so you can use the formula. LMK. Thank you.

    Quote Originally Posted by mvptomlinson View Post
    Do you want the original cell to be intact (leave the data after the second dash), or do you want it removed from the first cell and placed in the next cell? The first can be done using a formula, the second would require a macro.

  9. #9
    New Member
    Join Date
    Feb 2009
    Location
    Atlanta, GA
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Why can't you just do a text-to-columns on the original data column and delimit by "-". This will result in 3 columns. Sort the 3rd column so all spaces are moved to the bottom of the column.

  10. #10
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After second dash?

    Perhaps this, though there will be other ways of doing it...
    Code:
    =MID(A1,FIND("-",A1&"--",FIND("-",A1&"--",1)+1)+1,255)
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

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
  •