How to Find a Less-Than Symbol in an Excel Range Using Formulas
Question details
The user needs an Excel formula to detect if a specific range contains the less-than symbol (<) and output the symbol in another cell if it is found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Checking a range of cells for the presence of a specific mathematical symbol (<) and returning a text output based on the result.
- Observed behavior
- The goal state is to have a formula that successfully identifies either an exact match or a partial match of the '<' symbol within a specified cell range.
Identify the specific range of cells you want to check and decide whether you need to find cells containing only the less-than symbol or cells containing the symbol anywhere within their text.
Find Cells Exactly Matching the Less-Than Symbol
Use this method if you want to find cells where the entire content is exactly the '<' symbol, with no other text or numbers.
The COUNTIF function can count the number of cells that match a specific condition. By wrapping the '<' symbol in quotation marks, you force Excel to look for the literal character instead of treating it as a mathematical operator.
Click on the cell where you want the result to appear.
Type the formula =IF(COUNTIF(A1:A10,"<"),"<","") into the formula bar.
Replace A1:A10 with the actual target range of your data, then press Enter to execute the formula.
Find Cells Containing the Less-Than Symbol Anywhere
Use this approach with wildcard characters to detect the '<' symbol even if it is mixed with other text, operators, or numbers in the cell.
Use WPS Spreadsheet to Handle Complex Formulas Easily
WPS Office provides a highly compatible spreadsheet tool that fully supports standard Excel formulas, including COUNTIF, IF, and wildcard functions, making data analysis straightforward and efficient.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the document containing your data range.
- 2. Select the output cell: Click on an empty cell where you want the '<' symbol to appear if a match is found.
- 3. Apply the COUNTIF formula: Type =IF(COUNTIF(A1:A10,"*<*"),"<",""), adjust the range to fit your data, and press Enter.
Frequently Asked Questions
Why does COUNTIF treat the less-than symbol as an operator instead of text?
By default, COUNTIF interprets symbols like '<' or '>' as logical operators (e.g., less than a value). By wrapping the symbol in quotation marks or using wildcards (like "*<*"), you force the function to treat it as a literal text string instead.
Can I use this formula to find the greater-than symbol as well?
Yes. Simply replace the less-than symbol in the formula with a greater-than symbol (>), such as =IF(COUNTIF(A1:A10,"*>*"),">","").
What if I want it to return 'Found' or 'Not Found' instead of the symbol itself?
You can modify the output parameters of the IF function. Use =IF(COUNTIF(A1:A10,"*<*"),"Found","Not Found") to customize the result text according to your needs.
