VBA & Macro Problems

How to Check the 10th or 12th Character for a Hyphen in Excel VBA

John Wilson Oct 21, 2026 868 views

Question details

The user needs to evaluate whether a specific character (the 10th or 12th) in a cell's string is a hyphen using VBA.

How to Check the 10th or 12th Character for a Hyphen in Excel VBA

Product: Spreadsheet
Device & OS: not provided
Scenario: Writing a VBA macro to validate or parse specific string formats within a cell.
Observed behavior: Using wildcard comparisons returns 0 or fails because the wildcards are treated as literal text instead of correctly evaluating the specified character positions.

Before you start

Ensure that the Developer tab is enabled in your ribbon so you can access the VBA Editor to modify your macro script.

Solution 1Recommended

Use the VBA Mid Function to Check Specific Character Positions

The most reliable way to check a specific character's position in VBA is by extracting it using the Mid function instead of relying on wildcard characters.

When VBA evaluates strings using wildcards incorrectly, it attempts to match the literal asterisk (*) or question mark (?), leading to failed conditions (returning 0). By using the Mid function, you can isolate the exact character index and compare it as a standard string.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor.

Locate the target code

In the Project Explorer, double-click the module or worksheet containing the macro you want to edit.

Implement the Mid function

Replace your wildcard comparison with the Mid function in your If statement. Type: If Mid(rCel.Value, 10, 1) = "-" Or Mid(rCel.Value, 12, 1) = "-" Then

Complete the conditional block

Add your execution code inside the If block and close it with End If. Save and run your macro to successfully detect the hyphens.

Why this approach works: The syntax Mid(string, start_position, number_of_characters) precisely extracts exactly one character at the 10th or 12th position, guaranteeing an exact string match without wildcard interference.

Advanced Macro Support

Write and Execute VBA Macros Easily in WPS Spreadsheet

WPS Spreadsheet provides robust support for VBA macros, allowing you to run your string manipulation and automation scripts seamlessly with an interface you already know.

1. Open your workbook: Launch WPS Spreadsheet and open your .xlsm macro-enabled file.
2. Access the Developer Tab: Navigate to the 'Developer' tab located on the top ribbon.
3. Open the VBA Editor: Click 'Visual Basic' to open the built-in VBA editor.
4. Insert your script: Paste or type your Mid function code into the appropriate module.
5. Run the Macro: Click the 'Run' button or press F5 to execute the macro and process your data.

Fully compatible with Microsoft Excel VBA syntaxBuilt-in visual basic editor for easy scripting and debuggingLightweight and fast execution of complex macro loopsHighly compatible with .xlsm and .xlsb file formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does using the Like operator with wildcards fail in this scenario?

If wildcards like `*` or `?` are not formatted correctly within the VBA pattern string, the application evaluates them as literal characters rather than variable placeholders. Using the `Mid` function bypasses this issue entirely by referencing exact index numbers.

What is the syntax for the VBA Mid function?

The syntax is `Mid(text, start_num, num_chars)`. For example, `Mid("Apple", 2, 1)` starts at the 2nd character and extracts 1 character, returning 'p'.

Can I check for characters starting from the end of the string instead of the beginning?

Yes. You can use the `Right` function to extract characters from the end of the string, or combine the `Mid` function with the `Len` function (`Len(text) - n`) to dynamically count backward from the total string length.

Excel & Data Problems