How to Clean Mixed Text in Excel Using TEXTJOIN

Managing chaotic, mixed-text data in Excel remains a persistent manual bottleneck for database administrators. This challenge frequently arises when consolidating messy application exports from standard funding sources, such as federal portals or legacy corporate systems. Fortunately, leveraging TEXTJOIN with array filters grants users the ability to isolate, extract, and cleanly concatenate fragmented target substrings automatically. As a crucial stipulation, this advanced technique requires a modern Excel environment that supports dynamic arrays. Utilized successfully by research institutions to audit complex NIH grant codes, this method ensures error-free data formatting. Below, we examine the step-by-step formula construction to streamline your data-cleaning workflow.

Introduction to Text Cleaning with TEXTJOIN

In data analysis, clean data is the foundation of accurate reporting. However, we often encounter "dirty" or mixed text datasets imported from external databases, PDF files, or web scraping tools. These strings are frequently cluttered with unwanted numbers, symbols, erratic spacing, and punctuation marks. Examples include strings like "ID-9843_Active", " John Doe (123)", or "Product #90812!".

Historically, cleaning these strings required complex nested SUBSTITUTE functions, VBA macros, or Power Query. While Power Query is excellent, sometimes you need an on-sheet, dynamic formula solution. Thanks to Excel's modern calculation engine (Excel 2016 and later, specifically Microsoft 365), we can combine the powerhouse TEXTJOIN function with dynamic array functions like MID, SEQUENCE, LEN, and FILTER to build incredibly versatile text-cleaning formulas.

In this comprehensive guide, we will explore how to dissect, clean, and rebuild mixed text strings using TEXTJOIN-based formulas.

The Engine: Understanding the TEXTJOIN Array Pattern

Before diving into specific scenarios, it is crucial to understand the anatomy of a dynamic text-cleaning formula. The basic syntax of TEXTJOIN is:

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

To clean a text string, we must first break it down into individual characters, evaluate each character against specific rules (e.g., "Is it a letter?", "Is it a digit?"), discard the unwanted characters, and then glue the survivors back together. The universal pattern to convert a string in cell A2 into an array of individual characters is:

MID(A2, SEQUENCE(LEN(A2)), 1)

How does this work? Let's break it down:

• LEN(A2) calculates the total number of characters in the string.
• SEQUENCE(LEN(A2)) generates a sequential array of numbers from 1 to the length of the string (e.g., if the string is "Cat", it generates {1; 2; 3}).
• MID(A2, {1; 2; 3}, 1) extracts one character at a time starting from each position, outputting an array: {"C"; "a"; "t"}.

Once we have this array of characters, we can apply criteria-based filters and feed the resulting clean array directly back into TEXTJOIN.

Scenario 1: Extracting Only Numbers from Mixed Text

Suppose you have a column of product serial numbers and tracking codes mixed with letters and symbols (e.g., "Serial# 827-XYZ-491"), and you want to extract only the numeric digits.

The Formula:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))

How It Works:

1. The MID and SEQUENCE combination extracts each character into an array.
2. We multiply this array of characters by 1 (*1). When a numeric text character (like "8") is multiplied by 1, it successfully converts into a number (8). When an alphabetic character or symbol (like "S" or "-") is multiplied by 1, Excel returns a #VALUE! error.
3. The IFERROR function catches these #VALUE! errors and replaces them with an empty string ("").
4. Finally, TEXTJOIN concatenates the array. By setting the second argument to TRUE, TEXTJOIN ignores all empty strings, leaving only the successfully converted digits.

Before and After Comparison:

Scenario 2: Extracting Letters Only (Removing Numbers and Symbols)

If you need to do the exact opposite-strip out all numbers, punctuation, and spaces to leave only alphabetical characters (A-Z)-you can use Microsoft 365's FILTER and LET functions for an elegant, highly readable solution.

The Formula:

=LET(
    chars, MID(A2, SEQUENCE(LEN(A2)), 1),
    letters, FILTER(chars, ISERR(chars*1) * (chars <> " ")),
    TEXTJOIN("", TRUE, letters)
)

How It Works:

• LET allows us to define variables (like chars and letters) to avoid repeating long code segments.
• chars holds the split array of single characters from our input text.
• The FILTER function checks two conditions:
  1. ISERR(chars*1): This ensures that only non-numeric characters are kept (since numeric values won't produce an error when multiplied by 1).
  2. (chars <> " "): This excludes spaces. You can omit this condition if you want to preserve spaces between words.
  The asterisk (*) acts as an AND operator in array logic.
• TEXTJOIN merges the filtered array of clean alphabetical characters back into a single string.

Scenario 3: Removing Special Characters (Alphanumeric Only)

A common cleanup requirement is to remove punctuation, symbols, and special characters (like @, #, $, %, -, _, /) while preserving letters, numbers, and spaces. To achieve this, we can filter characters by checking their ASCII/Unicode character codes using the CODE function.

Uppercase letter codes range from 65 to 90 ('A' to 'Z'). Lowercase letters range from 97 to 122 ('a' to 'z'). Numbers range from 48 to 57 ('0' to '9').

The Formula:

=LET(
    chars, MID(A2, SEQUENCE(LEN(A2)), 1),
    clean_chars, FILTER(chars, 
        ISNUMBER(MATCH(CODE(UPPER(chars)), {32, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90}, 0)), 
        ""
    ),
    TEXTJOIN("", TRUE, clean_chars)
)

How It Works:

1. We convert the characters to uppercase using UPPER(chars) so we only need to test for codes 65 through 90 (ignoring lowercase code ranges). Code 32 is added to preserve spaces.
2. The array of codes {32, 48..57, 65..90} represents space, digits 0-9, and capital letters A-Z.
3. MATCH looks up the character codes of our input text against this whitelist of permitted ASCII codes. If a character is on the whitelist, MATCH returns its index position (a number). Otherwise, it returns #N/A.
4. ISNUMBER verifies if the lookup was successful. The FILTER function strips away any characters that failed the test, and TEXTJOIN glues the valid characters back together.

Scenario 4: Splitting and Rejoining with New Delimiters

What if you have text cluttered with inconsistent separators (such as semicolons, slashes, double spaces, and commas) and want to standardize them? For instance, transforming "Apple; Orange / Banana,Grapes" into a standardized comma-separated list.

With Microsoft 365, you can combine TEXTSPLIT and TEXTJOIN to execute a flawless clean-and-rebuild operation.

The Formula:

=TEXTJOIN(", ", TRUE, TEXTSPLIT(A2, {" ", ",", ";", "/"}, , TRUE))

How It Works:

• TEXTSPLIT(A2, {" ", ",", ";", "/"}) splits the string in A2 into an array using an array of delimiters. This handle spaces, commas, semicolons, and forward slashes.
• The last argument of TEXTSPLIT is set to TRUE, which instructs Excel to ignore empty values (preventing duplicate consecutive delimiters from creating blank entries in your array).
• TEXTJOIN(", ", TRUE, ...) joins the clean array back together, separating each element with a clean, standardized comma and space (", ").

Example:

If A2 contains "Red;; Blue // Green, Yellow", the formula converts it beautifully to "Red, Blue, Green, Yellow".

Summary of Best Practices

• Verify Excel Version: These dynamic array formulas work best in Microsoft 365, Excel 2021, and Excel for the Web. If you are on Excel 2019 or earlier, you may need to commit these formulas as legacy array formulas using Ctrl + Shift + Enter.
• Trimming Whitespace: To clean leading, trailing, or double internal spaces from your final output, wrap your formula in a TRIM() function.
• Formula Performance: Dynamic array formulas evaluated over tens of thousands of rows can slow down workbook calculation speeds. In massive datasets, it is advisable to calculate the cleaned columns once, copy the results, and paste them as values.

By leveraging the power of TEXTJOIN alongside LET and array manipulation, you can ditch messy VBA scripts and maintain a responsive, dynamic, and clean Excel worksheet.