Alex Campbell

asked 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

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

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