5 Ways To Find Excel Cells With Partial Text

Are you tired of scrolling through endless rows of data in Excel, searching for a specific piece of information? Look no further! Finding cells with partial text in Excel can be a daunting task, but there are several ways to make it easier. In this article, we'll explore five ways to find Excel cells with partial text, helping you to save time and boost your productivity.

Why Finding Partial Text is Important

When working with large datasets, it's common to need to find specific information within a cell. This information might be a name, a date, a phone number, or any other type of data. However, sometimes you might not know the exact text you're looking for, or you might only remember a part of it. That's where finding partial text comes in handy.

Method 1: Using the Find Feature

The most straightforward way to find Excel cells with partial text is by using the built-in Find feature.

To use the Find feature, follow these steps:

• Press Ctrl + F to open the Find dialog box.
• In the Find what field, enter the partial text you're looking for.
• Make sure the Look in field is set to Values.
• Click Find Next to start searching.

The Find feature will highlight the first cell that contains the partial text you entered. You can then use the Find Next and Find Previous buttons to navigate through the results.

Method 2: Using the Filter Feature

Another way to find Excel cells with partial text is by using the Filter feature.

To use the Filter feature, follow these steps:

• Select the range of cells you want to search.
• Go to the Data tab in the ribbon.
• Click Filter.
• In the filter dropdown menu, select Text Filters > Contains.
• Enter the partial text you're looking for.
• Click OK.

The Filter feature will hide all rows that don't contain the partial text you entered, making it easier to find the cells you're looking for.

Method 3: Using the VLOOKUP Function

If you need to find a cell with partial text and return a corresponding value from another column, you can use the VLOOKUP function.

To use the VLOOKUP function, follow these steps:

• Enter the VLOOKUP function in a new cell: VLOOKUP(A2, B:C, 2, FALSE)
• The first argument is the cell that contains the partial text.
• The second argument is the range of cells that contains the data you're looking for.
• The third argument is the column number that contains the corresponding value.
• The fourth argument is set to FALSE to perform an exact match.

The VLOOKUP function will return the corresponding value from the second column if the partial text is found.

Method 4: Using the INDEX-MATCH Function

Another way to find a cell with partial text and return a corresponding value from another column is by using the INDEX-MATCH function.

To use the INDEX-MATCH function, follow these steps:

• Enter the INDEX-MATCH function in a new cell: INDEX(C:C, MATCH(A2, B:B, 0))
• The first argument is the range of cells that contains the corresponding values.
• The second argument is the MATCH function, which searches for the partial text.
• The third argument is the range of cells that contains the data you're looking for.
• The fourth argument is set to 0 to perform an exact match.

The INDEX-MATCH function will return the corresponding value from the third column if the partial text is found.

Method 5: Using Power Query

If you're using Excel 2013 or later, you can use Power Query to find cells with partial text.

To use Power Query, follow these steps:

• Go to the Data tab in the ribbon.
• Click New Query > From Table/Range.
• Select the range of cells you want to search.
• In the Power Query Editor, click Add Column > Custom Column.
• Enter a formula that searches for the partial text: = Text.Contains([Column1], "Partial Text")
• Click OK.

The Power Query will create a new column that indicates whether the partial text is found in each cell.

Gallery of Excel Functions

Excel Functions for Finding Partial Text

Conclusion

Finding cells with partial text in Excel can be a challenging task, but there are several ways to make it easier. By using the Find feature, Filter feature, VLOOKUP function, INDEX-MATCH function, or Power Query, you can quickly and easily find the cells you're looking for. Whether you're a beginner or an advanced user, these methods will help you to boost your productivity and get the most out of your data.