How to extract all partial matches in Excel?
When working with large datasets in Excel, you may need to extract rows, cells, or data containing partial matches for a specific keyword or phrase. This task is common in data cleaning, filtering, and analysis. While Excel doesn’t have a built-in tool for directly extracting partial matches, you can achieve this using several methods. In this article, we’ll guide you through step-by-step methods to efficiently extract all partial matches in Excel.
- Extract all partial matches vertically with formula
- Extract all partial matches into a single cell with User Defined Function
- Extract all partial matches vertically or into a single cell with Kutools AI Aide
Extract all partial matches vertically with formula
To extract all matches vertically in a column based on a partial text, the following array formula can do you a favor:
Step1: Please copy and paste the below formula into a blank cell where you want to put the result:
=INDEX($A$2:$A$14,SMALL(IF(ISNUMBER((SEARCH($C$2,$A$2:$A$14))),MATCH(ROW($A$2:$A$14),ROW($A$2:$A$14)), ""),ROWS($E$1:E1)),COLUMNS($E$1:E1))Note: In the above formula, A2:A14 is the cells may contain the key word that you want to return matches from, C2 contains the key word, E1 is the cell above the formula.
Step2: Then press Ctrl + Shift + Enter keys simultaneously to get the first result, and then drag the fill handle down to get all matches until error value appears. At last, delete the error values, see screenshot:
Extract all partial matches into a single cell with User Defined Function
If you need to extract all matches into a single cell, separated by a specific delimiter, no formula alone can achieve this. Here, you need to apply the below User Defined Function:
Step1: Press "Alt + F11" keys to open the Microsoft Visual Basic for Applications window.
Step2: In the "Microsoft Visual Basic for Applications" window, click "Insert" > "Module", then copy the following code into the Module.
VBA code: Extract all partial matches into a single cell
Function ExtractPartMatch(rngInput As Range, rngSource As Range, Optional sDelimiter As String)
'Updateby ExtendOffice
Dim rng As Range
If sDelimiter = "" Then sDelimiter = ", "
For Each rng In rngSource
If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ExtractPartMatch = ExtractPartMatch & sDelimiter & rng.Value
Next
If Len(ExtractPartMatch) > 0 Then ExtractPartMatch = Mid(ExtractPartMatch, 2, Len(ExtractPartMatch))
End Function
Step3: After pasting the code, please go back to the worksheet where you want to locate the result, then enter this formula: =ExtractPartMatch(C2,$A$2:$A$14), and then, press Enter key to get the result as below screenshot shown:
Extract all partial matches vertically or into a single cell with Kutools AI Aide
With Kutools AI Aide, you can streamline this process and extract all partial matches in just a few clicks. This intelligent tool helps you identify and extract rows or cells containing specific keywords or phrases, saving you valuable time and ensuring accuracy.
After installing Kutools For Excel, please click "Kutools" > "AI Aide" to open the "Kutools AI Aide" pane.
- In the "Kutools AI Aide" pane, type the requirement in the chat box: such as:
Extract all partial matches vertically:
Extract the cells which contains "Wood" from column A and put them in column E, starting from cell E2
Extract all partial matches into a single cell:
Extract the cells which contains "Wood" from column A and put them in one cell E2 - And then, press Enter key or click the "Send" button. Kutools AI will analyze the question, after finishing, please click "Execute" button to get the result.
In conclusion, extracting partial matches in Excel can be accomplished through various methods tailored to your needs. Choose the method that best suits your workflow and streamline your Excel tasks! If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in