How to select text after dash if some text has one and some has two?

I am looking for an Excel 2010 function I can create to handle the following situations (I have not seen any text with three dashes):
Also, can't depend on ". -" because that is not consistent.

If one dash, want: Formulas > Formula Auditing > Error Checking
Text: Error Checking - Formulas > Formula Auditing > Error Checking

If two dashes, want: View > Macros > Use Relative References after the second dash.
Text: Use Relative References - Use relative references so that macros are recorded with actions relative to the initial selected cell. For instance, if you record a macro in cell A1 which moves the cursor to A3 with this option turned on, running the resulting macro in cell J6 would move the cursor to J8. If this option was turned off when the macro was recorded, running it in cell J6 would move the cursor to A3. - View > Macros > Use Relative References
LVL 1
Alex CampbellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
If I understand your request, you want to see all text after the last occurrence of a dash, or, to be specific, after a dash and space ("- ").

If the text is in cell A1, this function will work for one or two occurrences of a dash:
=MID(A1,IFERROR(FIND("- ",A1,FIND("- ",A1)+1),FIND("- ",A1))+2,LEN(A1))

Regards,
-Glenn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex CampbellAuthor Commented:
Very nice. I thought there would be a way to use the length, but you really pull it together. Thanks
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  It gets more complicated if there are three or more dashes, but I suspect that given your data set you won't see that.
Alex CampbellAuthor Commented:
The formula works, but I also found this UDF that is very handy:
How to use is below the coce.

If you use the function on the following text:

Text: Error Checking - Formulas > Formula Auditing > Error Checking

Function Target_Count(Target As String, rng As Range)
  Count = 0
  If Target = "" Then GoTo Done
   For Each c In rng
     N = InStr(1, c.Value, Target)
     While N <> 0
      Count = Count + 1
      N = InStr(N + 1, c.Value, Target)
     Wend
   Next c
 Target_Count = Count
Done:
End Function

Text	Char	Char Count	Formula
Error Checking - Formulas > Formula Auditing > Error Checking	A	1	=target_count(B2,A2)
Error Checking - Formulas > Formula Auditing > Error Checking	B	0	=target_count(B3,A3)
Error Checking - Formulas > Formula Auditing > Error Checking	C	2	=target_count(B4,A4)
Error Checking - Formulas > Formula Auditing > Error Checking	D	0	=target_count(B5,A5)
Error Checking - Formulas > Formula Auditing > Error Checking	E	2	=target_count(B6,A6)
Error Checking - Formulas > Formula Auditing > Error Checking	F	2	=target_count(B7,A7)
Error Checking - Formulas > Formula Auditing > Error Checking	G	0	=target_count(B8,A8)
Error Checking - Formulas > Formula Auditing > Error Checking	-	1	=target_count(B9,A9)
Error Checking - Formulas > Formula Auditing > Error Checking	>	2	=target_count(B10,A10)
Error Checking - Formulas > Formula Auditing > Error Checking	For	2	=target_count(B11,A11)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.