How to Categorize Bank Transactions by Partial Text in Excel
Question details
The user needs to group imported bank transactions by matching partial text in the descriptions to a standardized set of categories for easier tax reporting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing financial data for tax reporting by filtering, sorting, or summarizing transaction categories.
- Observed behavior
- Bank transactions have varied descriptions (e.g., 'gas January', 'gas March') that require a formula to automatically extract a shared keyword ('gas') and assign a unified category.
Ensure your bank transactions are imported into an Excel worksheet and compile a list of the unique category keywords (like 'gas', 'hire', etc.) that you want the formula to look for.
Use INDEX and SEARCH Formulas to Categorize Transactions
Extract partial text matches from transaction descriptions and assign them to a predefined list of categories using a combined array formula.
This method utilizes the SEARCH function to scan for specific keywords within your transaction descriptions. By combining it with ISNUMBER, MATCH, and INDEX, Excel automatically compares the description against your keyword list and returns the correct category.
In a blank area of your worksheet (for example, cells G2:G4), type the unique categories or keywords you want to find, such as 'gas' and 'hire'.
Insert a new column next to your transaction descriptions (e.g., Column E) to hold the extracted categories.
In the first cell of your new Category column (e.g., E2), enter the formula: =INDEX($G$2:$G$4,MATCH(TRUE,ISNUMBER(SEARCH($G$2:$G$4,C2)),0)). This assumes C2 contains your transaction description.
Press Enter, then click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this formula to all your imported bank transactions.
Once the categories are extracted, use this new column to easily sort the rows, apply data filters, or insert a PivotTable for your tax reporting.
Use WPS Spreadsheet to Seamlessly Categorize Bank Transactions
WPS Spreadsheet fully supports advanced array formulas like INDEX and SEARCH, allowing you to categorize, filter, and summarize your financial records efficiently. It provides a lightweight and free environment for all your tax preparation and data analysis tasks.
- 1. Open your transaction data: Launch WPS Spreadsheet and open the file containing your imported bank transactions.
- 2. Set up your categories: List your target keywords in a separate column range, just as you would in Microsoft Excel.
- 3. Apply the formula: Type the exact same =INDEX($G$2:$G$4,MATCH(TRUE,ISNUMBER(SEARCH($G$2:$G$4,C2)),0)) formula to extract categories from the descriptions.
- 4. Create a PivotTable: Navigate to the Insert tab and select PivotTable to organize and summarize your newly categorized financial data.
Frequently Asked Questions
What if a transaction description does not contain any of my listed keywords?
If no match is found, the formula will return an #N/A error. You can wrap your formula in an IFERROR function, such as =IFERROR(INDEX(...), "Uncategorized"), to automatically assign a default category for unmatched items.
Does the SEARCH function distinguish between uppercase and lowercase letters?
No, the SEARCH function is case-insensitive, meaning it will treat 'GAS' and 'gas' equally. If you require a case-sensitive match, replace SEARCH with the FIND function in your formula.
Can I use this method to categorize expenses in a PivotTable directly?
You cannot perform this array extraction natively inside the PivotTable. You must first create a helper column using the INDEX and SEARCH formula to extract the categories. Once populated, you can then add this new column to your PivotTable as a row or filter.
