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

Alex Campbell
Alex Campbell used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
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

Author

Commented:
Very nice. I thought there would be a way to use the length, but you really pull it together. Thanks
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial