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!

 

[excelbetter]

BINGO. Wow, I can't believe I missed that. So obvious. Thanks a bunch. Problem solved!

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.

 

[hutchisd]

No problem! Glad I could help

 

[Ron Coderre]

OK...Perhaps this, then (copied down):

B1: =MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",2)&"|")+1,255)

Does that help?

 

[Aladin Akyurek]

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.

Add a test expression to the suggested formula...

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))>1,TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1))),"")

 

[MikeWx]

The following will also give the results you are looking for. It is written to work with either Excel 2003 or Excel 2007.

=IF(ISERROR(FIND("^^",SUBSTITUTE(A1,"-","^^",2))),"BLANK SINCE THERE IS ONLY 1 DASH",MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",2)),LEN(A1)-FIND("^^",SUBSTITUTE(A1,"-","^^",2))+1))