How to Replace Double Question Marks with a Single Question Mark in Excel
Question details
The user wants to replace two consecutive question marks with a single literal question mark without triggering Excel's wildcard functionality.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the Find and Replace tool to clean up text data containing double question marks.
- Observed behavior
- Searching for ?? normally acts as a wildcard, finding and replacing unintended characters instead of just literal question marks.
Before modifying your data, keep in mind that the question mark (?) acts as a wildcard character in Excel, meaning it can represent any single character unless explicitly escaped.
Use the Tilde (~) Escape Character in Find and Replace
Use a tilde character to force Excel to treat the question mark as a standard text character rather than a wildcard.
In Excel, the question mark is a wildcard that matches any single character. To search for a literal question mark, you need to precede it with a tilde (~).
Press the shortcut Ctrl+H on your keyboard to open the Find and Replace dialog box.
In the 'Find what' box, type ~?~? to search for two literal question marks. The tilde escapes each question mark wildcard.
In the 'Replace with' box, type a single ? character.
Click the 'Replace All' button to replace all double question marks in your worksheet with a single one.
Edit and Clean Data Easily with WPS Spreadsheet
WPS Spreadsheet offers a powerful Find and Replace tool identical to Microsoft Excel, fully supporting wildcard characters and escape sequences. Clean your data efficiently while enjoying a free, lightweight office suite.
- 1. Open the Spreadsheet: Launch WPS Office and open your spreadsheet file.
- 2. Open Replace Dialog: Press Ctrl+H to open the Replace dialog box.
- 3. Enter the Escape Sequence: Type ~?~? in the 'Find what' field and a single ? in the 'Replace with' field.
- 4. Replace All: Click 'Replace All' to quickly update all literal question marks in your document.
Frequently Asked Questions
Why does Excel replace unintended characters when I search for a question mark?
Because the question mark is a built-in wildcard representing any single character. If you replace it without escaping, Excel will modify any character it finds. To target a literal question mark, you must escape it using a tilde (~?).
How do I replace other wildcard characters like an asterisk (*) in Excel?
Similar to the question mark, the asterisk is a wildcard that represents any number of characters. To find and replace a literal asterisk, put a tilde before it (~*) in the 'Find what' box.
What if I want to search for a literal tilde (~) in my spreadsheet?
Since the tilde is used as the escape character, you must escape the tilde itself. Enter two consecutive tildes (~~) in the 'Find what' box to search for a single literal tilde in your data.
