I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string

The issue is the end of the string in column A with different sizes at the end of the string - I have highlighted the ones I would like grouped to hopefully make it clearer


1 Answer 1


You can remove sizes from names with regexextract() in cell F1 like this:

=arrayformula( iferror( regexextract(A1:A, "(.+?)-[\dA-Z]"), A1:A ) )

Then query the data:

=query(A1:F, "select F, sum(B), sum(C), avg(D) where F is not null group by F", 1)

See your sample spreadsheet.

  • That's definitely along the right lines thanks - This was a simplified extract of the data I am working with - I have included a couple of exact strings which are more complicated below on the spreadsheet if you wouldn't mind having a look at those Essentially I would like all of the string apart from the data after the last ' - ' Thanks again
    – magelincs
    Commented May 27, 2022 at 9:31
  • 1
    Please ask only one question per post. To learn how to adjust the regex, see regularexpressions.info and RE2. Commented May 27, 2022 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.