Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-24
4
Medium Priority
?
77 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:Alex Campbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 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:Alex Campbell
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:Alex Campbell
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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