Power Query Problems

Extract Beneficiary Names from Excel Descriptions Using Power Query

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to extract specific beneficiary names from inconsistent bank payment descriptions containing mixed text, numbers, dates, and invoice references.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up bank statements or transaction logs where beneficiary information is buried inside unstructured text strings.
Observed behavior: Because the text strings have no consistent delimiters or patterns, standard text parsing tools fail to isolate the beneficiary names.

Before you start

Before starting in Power Query, prepare a comprehensive list of known client or beneficiary names in your workbook, as this irregular data requires a lookup reference to be parsed accurately.

Solution 1Recommended

Match Descriptions Against a Reference Table in Power Query

Since the text patterns are highly inconsistent, the most reliable method is to cross-reference the bank descriptions with a predefined list of known beneficiary names.

Without a fixed delimiter (like a comma or dash) or a consistent structural pattern, native extraction tools cannot isolate specific words. By using a reference table, you instruct Power Query to scan each description and extract any text string that perfectly matches an entry in your predefined beneficiary list.

Create a Beneficiary Reference Table

In your Excel workbook, set up a new table containing a single column with all known beneficiary and client names. Format it as an Excel Table (Ctrl+T) and name it 'Beneficiaries'.

Load Tables into Power Query

Select your main bank-payment data and go to the Data tab, then click 'From Table/Range' to load it into the Power Query Editor. Repeat this step for your 'Beneficiaries' table.

Add a Custom Search Column

In your main data query, navigate to Add Column > Custom Column. Write a formula utilizing List.Select and Text.Contains to check if any name from the Beneficiaries list appears within the description text.

Extract the Matched Name

Expand the newly created custom column to extract the matched beneficiary name as a text value. This effectively filters out random invoice numbers, dates, and irrelevant text.

Refine Rules and Load Data

Identify any blank or null results to spot missing beneficiaries. Add those missing names to your original Excel reference table, refresh the query, and click 'Close & Load' to output the cleaned data.

Case Sensitivity in Power Query: Power Query is strictly case-sensitive. Ensure you convert both your description text and reference list to lowercase during the query steps (using Text.Lower) to avoid missing valid matches due to capitalization differences.

Free Microsoft Office alternative

Looking for a Seamless Excel Alternative? Try WPS Office

If you frequently work with complex financial data, bank statements, or messy text descriptions, WPS Office provides a lightweight, highly compatible alternative to Microsoft Office. Enjoy advanced formulas, powerful data extraction tools, and seamless file management for free.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx bank payment files.
3. Clean Your Data: Utilize built-in text functions, advanced filters, and robust data tools to efficiently process your statements.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Rich library of text extraction and lookup formulasFree and lightweight with a familiar, easy-to-use interfaceSeamless migration of your existing spreadsheets without formatting loss

microsoft office alternative - wps office

Frequently Asked Questions

Why is a reference table necessary for extracting these names?

Because bank payment descriptions lack a consistent pattern, separator, or fixed position, automated tools cannot organically determine which part of the text is the name. A reference table explicitly tells Power Query what specific text strings to look for.

Can I extract names if there are minor spelling mistakes in the descriptions?

Standard text matching functions look for exact matches. To handle typos or slight spelling variations, you would need to implement Fuzzy Matching by merging queries and adjusting the similarity threshold in the Power Query merge settings.

What if a description contains multiple beneficiary names?

If a single description matches multiple entries in your reference table, Power Query will return a list. You can choose to expand this list to new rows, or extract values by concatenating them with a delimiter (like a comma) using the Text.Combine function.

Excel & Data Problems