Extract Text After second dash?

[excelbetter]

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!

 

[Gerald Higgins]

Just to be clear, what exactly do you want the results to be in your example ?

 

[Ron Coderre]

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:

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?

 

[excelbetter]

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.

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:

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?

 

[mvptomlinson]

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.

 

[excelbetter]

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

Just to be clear, what exactly do you want the results to be in your example ?

 

[mvptomlinson]

Perhaps:

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

 

[excelbetter]

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

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.

 

[hutchisd]

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.

 

[Gerald Higgins]

Perhaps this, though there will be other ways of doing it...

=MID(A1,FIND("-",A1&"--",FIND("-",A1&"--",1)+1)+1,255)