Just to be clear, what exactly do you want the results to be in your example ?
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!
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
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:
Copy that formula down through B8.Code:B1: =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))
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
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.
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.
Perhaps:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))<=1,"",MID(SUBSTITUTE(A1,"-","^^",2),FIND("^^",SUBSTITUTE(A1,"-","^^",2))+2,LEN(A1)))
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.
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
Like this thread? Share it with others