Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Combining Text Cells

Copper Contributor

Hello,

 

I have a spread sheet that has text in different cells. The cells are sorted by day of the week. I would like to combine all of the text cells into one cell on another sheet based on the day of the week. Similar to the SUMIF function for summing up numbers for a category. I can't seem to find a formula that does that.

 

not_ashuma_0-1696692241440.png

I would like a formula that puts all the descriptions for Tuesday into one cell on another sheet. Repeat the process for the different days of the week. By date is fine too.

6 Replies

@not_ashuma 

You can use TEXTJOIN and FILTER:

 

=TEXTJOIN(" ". TRUE, FILTER(description_range, day_range="Tuesday", ""))

@not_ashuma 

Here's my approach:

=LET(
    filtered, FILTER(Table1[Description:], Table1[Day:] = B2, ""),
    linebreak, CHAR(10),
    TEXTJOIN(linebreak, , filtered)
)

Can you do your solution without a table? My data isnt in a table currently. When I put it into a table it messes up the format pretty badly.

@Patrick2788 

@not_ashuma 

Yes, here's a version with no table that uses dynamic named items. The formula is a bit shorter:

=LET(
    filtered, FILTER(description, day = B2, ""),
    linebreak, CHAR(10),
    TEXTJOIN(linebreak, , filtered)
)

 

What am I doing wrong here?

 

not_ashuma_0-1696724793376.png

 

 

@Patrick2788 

@not_ashuma 

Does your copy of the workbook have these dynamic named items?

Patrick2788_0-1696762121866.png