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
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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
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
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)
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER