Highlight Matching Stock Records Using Excel Conditional Formatting
Question details
The user needs to highlight cells in one column that contain company names matching those in another column, specifically ignoring the stock ticker symbol enclosed in parentheses.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing two lists of company records where one list includes additional stock ticker information appended to the company name, requiring a partial text match.
- Observed behavior
- A standard exact-match conditional formatting rule fails because of the extra ticker text (e.g., '(XLON:SPA)'), so the extra text must be ignored to identify true matches.
Ensure both lists of company names are located within the same worksheet, and identify the exact column letters for your stock records and target company names to adjust the formula correctly.
Use TEXTBEFORE and COUNTIF in a Custom Formatting Rule
Apply a conditional formatting formula using TEXTBEFORE to strip the ticker symbol and COUNTIF to find matches in the target column.
This method uses the TEXTBEFORE function to strip out the stock ticker (e.g., '(XLON:SPA)') by removing everything from the opening parenthesis onwards. The COUNTIF function then takes this cleaned company name and checks if it exists anywhere in the target column.
Highlight the range of cells in your column that contains the company names with tickers (for example, C2:C100).
Go to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and then select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.
In the formula bar, type `=COUNTIF($D:$D,TRIM(TEXTBEFORE(C2,"(")))>0`. Replace 'C2' with the first cell in your selected range, and '$D:$D' with the column containing the clean company names you want to match against.
Click the 'Format' button, navigate to the 'Fill' tab, choose a highlight color, and click 'OK' twice to apply the rule.

Highlight Partial Matches Effortlessly in WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for advanced conditional formatting and modern text functions, making it incredibly easy to analyze stock data, compare lists, and format cells based on partial text matches.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your stock records.
- 2. Select the data: Highlight the column containing the company names with ticker symbols.
- 3. Create a new rule: Navigate to Home > Conditional Formatting > New Rule.
- 4. Apply the formula: Choose the formula option, enter your COUNTIF and TEXTBEFORE combination, set a background fill color, and click OK.

Frequently Asked Questions
What if my Excel version does not have the TEXTBEFORE function?
For older versions of Excel, you can use a combination of the LEFT and FIND functions. The equivalent formula inside COUNTIF would be `=COUNTIF($D:$D, TRIM(LEFT(C2, FIND("(", C2)-1)))>0`.
Can I highlight the entire row instead of just the specific cell?
Yes. Select the entire data range (e.g., A2:D100) before creating the conditional formatting rule. Ensure your formula uses an absolute column reference for the criteria cell, such as `$C2`, so the formatting spans across the entire row.
Why isn't my conditional formatting formula highlighting matches?
This usually happens if there are hidden trailing spaces in your data or if the cell references in the formula do not align with the top-left cell of your selected range. Ensure you use the TRIM function as shown in the formula, and verify that the formula references the first active cell in your current selection.




