List Banned Wallet Addresses
I need to create a comma-separated list of all wallet addresses flagged as 'Scammer' for the blacklist. This list will be used to update our platform's automated banning system.
=TEXTJOIN(", ", TRUE, IF(B2:B5="Scammer", A2:A5, "")) How it works: This formula leverages `TEXTJOIN` to concatenate text strings with a specified delimiter. The `IF` function acts as a filter, creating an array where only wallet addresses with a 'Scammer' status are present, and all other entries are empty strings. By setting the `ignore_empty` argument of `TEXTJOIN` to `TRUE`, the function efficiently skips these empty strings, ensuring that only the relevant scammer wallet addresses are joined together, resulting in a clean, comma-separated blacklist.
Data Setup
| Wallet Address | Status |
|---|---|
| 0xabc123 | Scammer |
| 0xdef456 | Legit |
| 0xghi789 | Scammer |
| 0xjkl012 | Scammer |
Step-by-Step Guide
Identify the column containing the wallet addresses (e.g., Column A) and the column indicating their status (e.g., Column B).
In an empty cell, start typing `=TEXTJOIN(`. The first argument is the delimiter. For a comma-separated list with spaces, use `", "`.
The second argument is `ignore_empty`. Since we only want to include specific addresses and will use an `IF` statement that might return empty strings, set this to `TRUE` to prevent extra delimiters.
For the `text` argument, use an `IF` statement to filter the addresses: `IF(B2:B5="Scammer", A2:A5, "")`. This checks if the status in column B is 'Scammer' for each row from 2 to 5. If true, it returns the corresponding wallet address from column A; otherwise, it returns an empty string.
Combine all parts: `=TEXTJOIN(", ", TRUE, IF(B2:B5="Scammer", A2:A5, ""))`.
Press `Enter`. Excel will automatically handle this as an array formula in modern versions, generating your comma-separated list.