How to Use the TEXTJOIN and REGEXREPLACE Functions in Google Sheets to Sanitize Delimited Strings

Data analysts and operations teams frequently encounter messy spreadsheets where customer tags, product SKU codes, email lists, or transaction notes are scattered across multiple columns or cluttered with erratic punctuation, trailing commas, and repeated delimiters. Concatenating these values using the basic ampersand (&) or CONCATENATE function inevitably produces redundant delimiters (such as Apple,,Banana,,,Orange) when blank cells exist. Combining the TEXTJOIN function with the regular expression power of REGEXREPLACE allows Google Sheets users to merge multi-cell data arrays while stripping unwanted whitespace, collapsing repeated separators, and sanitizing strings in a single calculation.

Understanding the Complementary Roles of TEXTJOIN and REGEXREPLACE

Both functions perform distinct transformations that become exponentially more powerful when nested together:

• TEXTJOIN: Merges multiple strings or cell ranges into a single unified string using a specified delimiter (e.g. a comma or pipe), with a native boolean flag to automatically ignore empty cells.
• REGEXREPLACE: Evaluates a text string against a regular expression pattern and replaces matching occurrences with a replacement string, enabling pattern-based cleansing of irregular characters, consecutive spaces, and trailing symbols.

Constructing the Baseline TEXTJOIN Concatenation

The syntax for TEXTJOIN requires three primary arguments:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

Assume you have customer tags listed horizontally in cells A2:E2, containing some blank cells and irregular entries:

=TEXTJOIN(", ", TRUE, A2:E2)

Setting ignore_empty to TRUE ensures Google Sheets skips blank cells without inserting consecutive commas. However, if individual cells contain internal trailing spaces, extra punctuation, or formatting artifacts, TEXTJOIN alone cannot clean the internal text structure.

Sanitizing Delimited Text Using REGEXREPLACE

By nesting TEXTJOIN inside REGEXREPLACE, you can sanitize the resulting consolidated string against specific formatting rules.

1. Collapsing Consecutive Delimiters and Formatting Gaps

If imported data contains stray commas or punctuation that bypasses empty-cell checks, collapse multiple consecutive separators into a single clean comma and space:

=REGEXREPLACE(TEXTJOIN(", ", TRUE, A2:E2), "[,\s]+,", ",")

Explanation of the regex pattern:

• [,\s]+, searches for any combination of commas and whitespace followed immediately by another comma.
• The replacement string "," consolidates them into a single delimiter.

2. Stripping Leading and Trailing Delimiters

When consolidating data fragments, strings frequently end up with stray punctuation at the start or conclusion. Strip trailing and leading commas using boundary anchors:

=REGEXREPLACE(TEXTJOIN(", ", TRUE, A2:E2), "(^[,\s]+|[,\s]+$)", "")

Explanation of pattern elements:

• ^[,\s]+ matches any commas or whitespace at the beginning (^) of the string.
• | acts as a logical OR.
• [,\s]+$ matches any commas or whitespace at the absolute end ($) of the string.
• Replacing with "" removes edge artifacts cleanly.

3. Standardizing SKU Codes and Alphanumeric Identifiers

When aggregating inventory product codes (e.g. AB-101, CD_202, EF 303) where separators vary between dashes, underscores, and spaces, standardize all internal separators into uniform hyphens:

=REGEXREPLACE(TEXTJOIN("; ", TRUE, A2:E2), "[_\s]+", "-")

Applying ArrayFormula for Automated Column-Wide Cleansing

To process hundreds of client records down an entire column without manually dragging formulas, incorporate MAP and LAMBDA (or BYROW):

=BYROW(A2:E50, LAMBDA(row, 
  REGEXREPLACE(
    TEXTJOIN(", ", TRUE, row), 
    "(^[,\s]+|[,\s]+$)", 
    ""
  )
))

This dynamic array formula evaluates each row independently, sanitizes the concatenated text, and spills clean, standardized strings down the target column automatically.

Auditing and Validating Cleansed Results

To verify that data cleansing executed successfully without leaving hidden non-printing characters:

• Wrap the formula inside LEN() to verify character counts against raw text.
• Use TRIM() as a final exterior wrapper to ensure all ASCII spaces are normalized.

Pairing TEXTJOIN with REGEXREPLACE provides a versatile, robust framework for string hygiene and data transformation directly within Google Sheets.