How to Group Matching Filenames into One Excel Cell
Question details
The user needs to group a list of photograph filenames by their collection number prefix and combine all matching filenames into a single cell.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and consolidating a list of files where items sharing the same prefix must be concatenated into a single cell for easier tracking or reporting.
- Observed behavior
- Filenames are currently listed individually in separate rows and need to be grouped and joined based on a shared prefix delimiter.
Ensure your filename data is clean and consistently uses the same delimiter (such as a hyphen or underscore) to separate the prefix from the rest of the filename.
Use the GROUPBY and TEXTBEFORE Functions (Excel 365)
If you are using a modern version of Excel (Microsoft 365), you can use a combination of dynamic array functions to quickly group and join text.
The GROUPBY function is a powerful dynamic array tool that allows you to group data and apply an aggregation function, such as ARRAYTOTEXT, to combine text strings.
Click on an empty cell where you want the grouped results to appear.
Type the formula: =LET(names, $A$1:$A$29, GROUPBY(TEXTBEFORE(names, "-"), names, ARRAYTOTEXT, 3, 0)). Adjust the range $A$1:$A$29 to match your actual data, and change the "-" if your prefix uses a different delimiter.
Press Enter. The formula will automatically extract the prefix, group identical prefixes together, and concatenate the corresponding filenames into a single cell separated by commas.

Use Power Query to Group and Combine Text
Power Query is a robust alternative that works across many Excel versions and is ideal for handling large datasets without relying on complex dynamic array formulas.
Organize and Group Your Data Efficiently with WPS Office
WPS Spreadsheet provides a comprehensive set of functions and data tools, making it easy to manipulate text, split strings, and organize complex lists without needing an expensive subscription.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your list of filenames.
- 2. Separate the prefixes: Use built-in functions like LEFT and FIND, or the 'Text to Columns' tool under the Data tab to separate your prefixes into a new column.
- 3. Combine the data: Use the TEXTJOIN function combined with IF array formulas to quickly group and concatenate the filenames based on the separated prefixes.

Frequently Asked Questions
What if my filenames have different delimiters?
If your data uses inconsistent delimiters (like a mix of hyphens and underscores), it is highly recommended to use the 'Find and Replace' tool (Ctrl+H) to standardize all delimiters to a single character before applying grouping formulas or Power Query.
Can I use TEXTJOIN instead of GROUPBY to combine filenames?
Yes, in versions of Excel that support TEXTJOIN but not GROUPBY, you can combine TEXTJOIN with the FILTER function. For example: =TEXTJOIN(", ", TRUE, FILTER(names, LEFT(names, LEN("prefix"))="prefix")). You will need to do this for each unique prefix.
Why does the GROUPBY formula return a #NAME? error?
The #NAME? error occurs when Excel does not recognize the function. The GROUPBY function is a recent addition to Microsoft 365. If you are using an older version like Excel 2019 or 2016, you will need to use Power Query or VBA instead.




