Formatting Issues

Highlight Matching Stock Records Using Excel Conditional Formatting

Camila MilosovichCamila Milosovich Nov 11, 2026 868 views

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.

Highlight Matching Stock Records Using Excel Conditional Formatting
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target range

Highlight the range of cells in your column that contains the company names with tickers (for example, C2:C100).

2
Open Conditional Formatting

Go to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and then select 'New Rule' from the dropdown menu.

3
Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

4
Enter the match formula

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.

5
Apply formatting

Click the 'Format' button, navigate to the 'Fill' tab, choose a highlight color, and click 'OK' twice to apply the rule.

Use TEXTBEFORE and COUNTIF in a Custom Formatting Rule
Function Availability: The TEXTBEFORE function is available in Microsoft 365 and modern spreadsheet software. If you are using an older version, use the LEFT and FIND functions instead: =COUNTIF($D:$D,TRIM(LEFT(C2,FIND("(",C2)-1)))>0.
Advanced Data Formatting

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. 1. Open your file: Launch WPS Spreadsheet and open the document containing your stock records.
  2. 2. Select the data: Highlight the column containing the company names with ticker symbols.
  3. 3. Create a new rule: Navigate to Home > Conditional Formatting > New Rule.
  4. 4. Apply the formula: Choose the formula option, enter your COUNTIF and TEXTBEFORE combination, set a background fill color, and click OK.
Fully compatible with Microsoft Excel formulas and conditional formatting rules.Lightweight and fast, even when processing large datasets of stock records.Intuitive user interface that makes setting up complex formatting rules straightforward.Free to download with comprehensive cross-platform support.
microsoft office alternative - wps office

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.