Solved

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

Posted on 2016-09-24
4
61 Views
Last Modified: 2016-09-25
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
0
Comment
Question by:Alex972
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41814267
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
0
 
LVL 1

Author Closing Comment

by:Alex972
ID: 41814647
Very nice. I thought there would be a way to use the length, but you really pull it together. Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41814821
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.
0
 
LVL 1

Author Comment

by:Alex972
ID: 41815013
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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question