Microsoft Excel is a powerful tool that helps users manage and analyze data efficiently. When working with large datasets, it’s common to encounter scenarios where you need to extract partial matches. In this guide, we’ll explore several methods to achieve this in Excel.
Method 1: Using the FILTER Function
Step 1: Organize Your Data
Ensure your data is well-organized in a worksheet. For this method to work effectively, it’s crucial that your dataset is structured with a clear header row and the relevant data.
Step 2: Create a New Column
In an empty column next to your dataset, create a new column where you want the partial matches to appear.
Step 3: Utilize the FILTER Function
In the first cell of the new column, enter the following formula:
=FILTER(original_range, ISNUMBER(SEARCH("partial_text", original_range)))
Replace “original_range” with the actual range of your data and “partial_text” with the specific text you’re searching for.
Step 4: Press Enter
After entering the formula, press Enter. The new column will now display only the rows that contain the partial text you specified.
Method 2: Text to Columns
Step 1: Select Your Data
Highlight the column containing the data from which you want to extract partial matches.
Step 2: Access the Text to Columns Wizard
Navigate to the “Data” tab and click on “Text to Columns.” This opens the Text to Columns Wizard.
Step 3: Choose Delimited
Select “Delimited” in the wizard and click “Next.”
Step 4: Select Delimiters
Choose the delimiter that separates your text (e.g., space, comma) and click “Next.”
Step 5: Format the Destination
Specify the destination for the split data. Choose “Finish” to complete the process.
Step 6: Filter the Partial Matches
In a new column, use the FILTER function or any other preferred method to filter rows containing the desired partial matches.
Method 3: Conditional Formatting
Step 1: Select Your Data
Highlight the range of cells where you want to identify partial matches.
Step 2: Access Conditional Formatting
Go to the “Home” tab, click on “Conditional Formatting,” and choose “New Rule.“
Step 3: Use a Formula
Select “Use a formula to determine which cells to format” and enter the following formula:
=ISNUMBER(SEARCH("partial_text", A1))
Replace “partial_text” with the specific text you’re searching for, and adjust the cell reference (A1) accordingly.
Step 4: Format Cells
Specify the formatting style for the cells containing the partial matches and click “OK.”
Step 5: Review Results
The cells with partial matches will now be formatted as per your chosen style.
Method 4: Using the TEXTJOIN Function
Step 1: Organize Your Data
Ensure your data is structured with a clear header row and the relevant data.
Step 2: Create a New Column
In an empty column next to your dataset, create a new column where you want the concatenated partial matches to appear.
Step 3: Utilize the TEXTJOIN Function
In the first cell of the new column, enter the following formula:
=TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH(“partial_text”, original_range)), original_range, “”))
Replace “partial_text” with the specific text you’re searching for, and “original_range” with the actual range of your data.
Step 4: Press Ctrl+Shift+Enter
To execute the formula as an array formula, press Ctrl+Shift+Enter. The new column will display a concatenated list of partial matches.
Use VBA Code to extract all partial matches
1. Press Alt + F11 to open the VBA editor in Excel.
2. Insert a new module by selecting Insert, and then choosing Module.
3. Copy and paste the code into the module.
Sub ExtractPartialMatchesToNewSheet()
Dim ws As Worksheet
Dim originalRange As Range
Dim matchesSheet As Worksheet
Dim resultRange As Range
Dim partialText As String
Dim cell As Range
Dim newRow As Long
' Use the active sheet
Set ws = ActiveSheet
' Prompt the user to select the original range
On Error Resume Next
Set originalRange = Application.InputBox("Select the range to search for partial matches", Type:=8)
On Error GoTo 0
' Exit if the user cancels the selection
If originalRange Is Nothing Then
MsgBox "Operation canceled."
Exit Sub
End If
' Create a new sheet called "Matches" or use an existing one
On Error Resume Next
Set matchesSheet = Sheets("Matches")
On Error GoTo 0
' If "Matches" sheet doesn't exist, create it
If matchesSheet Is Nothing Then
Set matchesSheet = Sheets.Add(After:=Sheets(Sheets.Count))
matchesSheet.Name = "Matches"
End If
' Set the result range in the "Matches" sheet
Set resultRange = matchesSheet.Cells(matchesSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
' Specify the partial text you're searching for
partialText = InputBox("Enter the partial text to search for")
' Clear previous results in the "Matches" sheet
matchesSheet.Cells.Clear
' Loop through each cell in the original range
For Each cell In originalRange
' Check if the cell contains the partial text
If InStr(1, cell.Value, partialText, vbTextCompare) > 0 Then
' If a partial match is found, add it to the result range in the "Matches" sheet
resultRange.Value = cell.Value
' Move to the next row in the result range
Set resultRange = resultRange.Offset(1, 0)
End If
Next cell
' Notify the user about the results
MsgBox "Partial matches have been extracted to the 'Matches' sheet."
End Sub
4. Close the VBA editor.
5. Run the Macro by navigating to the developer tab> Macros then run the Macro named ExtractPartialMatchesToNewSheet()
On the next screen use the mouse to select the range with the data you want to extract exact partial matches from and press okay
A new dialogue box will pop up on a new sheet called Matches prompting you to type the partial match
Once you press ok on the above step then all the exact matches will be extracted on your new sheet automatically.
