Solved

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

Posted on 2016-09-24
4
70 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 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: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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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