Function Problems

How to Group Matching Filenames into One Excel Cell

Steve KSteve K Nov 10, 2026 868 views

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.

How to Group Matching Filenames into One Excel 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.
Before you start

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.

Solution 1Recommended

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.

1
Select an output cell

Click on an empty cell where you want the grouped results to appear.

2
Enter the dynamic formula

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.

3
Generate the results

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 the GROUPBY and TEXTBEFORE Functions (Excel 365)
Version Compatibility: The GROUPBY function is available in newer versions of Excel 365. If you see a #NAME? error, your version of Excel may not support this function yet.

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. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your list of filenames.
  2. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced array functions and text manipulation formulasBuilt-in powerful data processing tools for quick groupingLightweight application with a user-friendly interface
microsoft office alternative - wps office

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.