Finding all text to the right a the second hyphen

[termeric]

Hello, i'm trying to figure out how to isolate the text to on the right side after the second hyphen. my text string looks like this Category - Name - ID - SubID so i want to only take ID and Sub ID. how can count the number of characters to the right of the second hyphen from the right?

 

[Fluff]

How about

=TRIM(MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",2))+1,99))

 

[mumps]

Try: =MID(A1,FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),2))+2,9999)

 

[termeric]

i appreciate the responses, i forgot to mention that i'm trying to do this in VBA. will this still work?

 

[Fluff]

How about

Sub termeric()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 1).Value = Trim(Split(Cl, "-", 3)(2))
   Next Cl
End Sub

If you can a cell with only 1 hyphen this will fail, let me know if that is a possibility.

 

[termeric]

How about

Sub termeric()
   Dim Cl As Range
  
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 1).Value = Trim(Split(Cl, "-", 3)(2))
   Next Cl
End Sub

If you can a cell with only 1 hyphen this will fail, let me know if that is a possibility.

This works, i had to modify it a bit so that i am taking the ID & SubID and the hypehen between

Cl.Offset(, 1).Value = Trim(Split(Cl, "-", 3)(1) & "-" & Trim(Split(Cl, "-", 3)(2)))
[\code]

much appreciated

 

[Fluff]

There should have been no need to modify it, this is what I get

+Fluff 1.xlsm
	A	B
1
2	Category - Name - ID - SubID	ID - SubID
Main

If you are only getting the SubId, then you do not have 2 - before the ID. I suspect the the hyphen between the Category & Name is not a true hyphen.

 

[termeric]

is it possible to read this from the right instead of the left? i know the back two are hyphens

 

[Fluff]

can you post som actual samples of your data, they way we can find out what the other - actually is?

 

[termeric]

[Middle Market] New Invoice for Customer AAA-86580531-2612021114547
Public & Labor- New Invoice for Customer BBB-0838904-2612021134611
Middle Market- New Invoice for Customer CCC-143257-261202114184
Middle Market- New Invoice for Customer CCC-143257-261202114316