Combine Abandoned Cart Emails
I need to join all emails with 'Cart Status' = 'Abandoned' to copy into my newsletter tool.
=TEXTJOIN(", ", TRUE, IF(B2:B7="Abandoned", A2:A7, "")) How it works: The `TEXTJOIN` function is perfect for concatenating text strings from a range, especially when you need a specific delimiter. In this scenario, we use an `IF` statement as the `text` argument for `TEXTJOIN`. The `IF` statement acts as a filter: it checks each row's 'Cart Status' (B2:B7). If the status is "Abandoned", it passes the corresponding 'Email' (A2:A7) to `TEXTJOIN`. If not, it passes an empty string. The `TRUE` argument for `ignore_empty` ensures that these empty strings are not included in the final output, resulting in a clean, comma-separated list of only the abandoned cart emails.
Data Setup
| Cart Status | |
|---|---|
| john.doe@example.com | Purchased |
| jane.smith@example.com | Abandoned |
| peter.jones@example.com | Purchased |
| sara.lee@example.com | Abandoned |
| mike.brown@example.com | Abandoned |
| lisa.white@example.com | Purchased |
Step-by-Step Guide
Identify the column containing email addresses (e.g., Column A) and the column containing cart statuses (e.g., Column B).
In a blank cell, start typing the TEXTJOIN function: `=TEXTJOIN(`.
For the `delimiter` argument, enter `", "` (a comma followed by a space, enclosed in double quotes) to separate the email addresses.
For the `ignore_empty` argument, type `TRUE` to ensure that only actual email addresses are included and empty strings (from non-abandoned carts) are ignored.
For the `text1` argument, construct an `IF` statement: `IF(B2:B7="Abandoned", A2:A7, "")`. This checks each cell in the 'Cart Status' range (B2:B7); if it equals "Abandoned", it returns the corresponding email from the 'Email' range (A2:A7); otherwise, it returns an empty string.
Close the TEXTJOIN function: `)`.
Press Enter. The cell will now display a single string of all abandoned cart emails, separated by ", ".