'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use and Search for the Tilde Character in Excel
Question details
The user needs to find, replace, or match a literal tilde (~) character in Excel formulas and search functions without triggering wildcard behaviors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a literal tilde in a dataset or using lookup formulas where the reference data contains a tilde.
- Observed behavior
- Excel treats the single tilde as an escape character for wildcards, leading to failed searches or lookup errors when trying to match a literal tilde.
Verify whether the tilde in your data is used as a standard text character or if you are specifically trying to locate wildcard characters like asterisks (*) or question marks (?).
Search and Replace a Literal Tilde
Use a double tilde in the Find and Replace dialog to search for a single literal tilde.
In Excel, the tilde (~) is a special escape character used to indicate that the following character should be treated literally, not as a wildcard. To tell Excel you are looking for an actual tilde, you must escape the tilde with another tilde.
Press Ctrl + F on your keyboard to open the Find and Replace dialog box.
In the 'Find what' input field, type two consecutive tildes (~~).
Click 'Find Next' to locate instances of a single tilde, or switch to the 'Replace' tab to swap literal tildes with your desired text.
Escape the Tilde in VLOOKUP and Match Formulas
Use the SUBSTITUTE function within your lookup formulas to double the tilde, preventing Excel from treating it as a wildcard.
Handle Wildcards and Formulas Seamlessly in WPS Office
WPS Spreadsheet fully supports advanced formula functions, wildcard searches, and escape characters like the tilde. You can manage complex data matching and lookup functions just as smoothly as in Microsoft Excel.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Find Literal Tildes: Press Ctrl + F, type ~~ in the search box, and click Find Next.
- 3. Use Lookup Formulas: Type your lookup formula, such as =VLOOKUP(SUBSTITUTE(D1,"~","~~"),A:B,2,0), to accurately match data.
Frequently Asked Questions
Why does Excel use the tilde as an escape character?
In Excel, the asterisk (*) and question mark (?) are wildcard characters used for partial text matches. The tilde (~) is placed before these wildcards to indicate that you want to search for the actual asterisk or question mark as standard text, rather than using them as wildcards.
How do I search for a literal question mark or asterisk in Excel?
To search for a literal question mark or asterisk, type a tilde followed by the character in the Find box. For example, type ~? to find a question mark, and ~* to find an asterisk.
Can I disable wildcards in Excel VLOOKUP?
No, VLOOKUP natively utilizes wildcards when the match type is set to exact (0 or FALSE). You cannot turn off this feature. If your lookup values contain wildcards or tildes, you must use the SUBSTITUTE function to escape them.
