Extracting Capitalized Words in Excel Using TEXTSPLIT and EXACT

Manually isolating uppercase words from mixed-case text strings is a tedious, error-prone struggle for database administrators. While standard funding sources like corporate IT budgets often restrict access to specialized data-cleansing software, mastering native Excel functions offers a powerful alternative. This approach grants analysts immediate data-filtering autonomy without relying on external tools.

Stipulation: This technique requires Excel 365 or Excel 2021 to support dynamic array functionality. For example, extracting "UNICEF" from "a UNICEF initiative" can now be automated instantly. Below, we will demonstrate how to construct the formula using TEXTSPLIT, EXACT, and FILTER to streamline your workflow.

In text processing and data cleaning, extracting specific types of words from a sentence is a common challenge. One frequent task is extracting capitalized words-such as proper nouns (names of people, places, or brands) or acronyms (like NASA or FBI)-from a block of unstructured text.

Historically, solving this problem in Excel required complex, slow VBA macros or highly convoluted legacy array formulas. However, with the introduction of modern Excel 365 functions, you can build elegant, dynamic, and fully automated formulas to extract capitalized words. By combining TEXTSPLIT, EXACT, and helper functions like FILTER and LET, you can isolate case-sensitive text with surgical precision.

This article provides a comprehensive guide on how to build, refine, and apply an Excel formula to extract capitalized words, walking you through the logic step-by-step.

The Logic Behind Case Sensitivity in Excel

Before writing the formula, it is essential to understand why this task requires a specialized approach. Excel is, by default, case-insensitive. If you write the logical expression ="A"="a", Excel returns TRUE. Similarly, functions like SEARCH, REPLACE, and standard comparison operators ignore capitalization.

To overcome this limitation, we must use functions that are strictly case-sensitive. The primary tool for this is EXACT. The EXACT function compares two text strings and returns TRUE only if they are identical, including their casing. For example:

• =EXACT("Excel", "excel") returns FALSE
• =EXACT("Excel", "Excel") returns TRUE

The Core Functions Explained

To extract capitalized words, we will stitch together several modern Excel functions. Here is the role of each component:

• TEXTSPLIT: Splits a text string into an array of individual words using a delimiter (such as a space " ").
• EXACT: Performs the case-sensitive check to determine if a character or word matches our capitalization criteria.
• LEFT: Extracts the first character of each split word so we can evaluate if it begins with an uppercase letter.
• UPPER: Converts characters to uppercase, which provides the baseline comparison for EXACT.
• FILTER: Screens the array of split words, retaining only those that pass our case-sensitivity test.
• TEXTJOIN: (Optional) Recombines the extracted array of words back into a single, comma-separated or space-separated string.

Scenario 1: Extracting Words Starting with a Capital Letter

Let's start with the most common scenario: extracting words that begin with a capital letter (e.g., "John", "London", "Microsoft").

The Basic Formula

If your source text is in cell A2, the fundamental formula is:

=FILTER(TEXTSPLIT(A2, " "), EXACT(LEFT(TEXTSPLIT(A2, " "), 1), UPPER(LEFT(TEXTSPLIT(A2, " "), 1))))

How It Works

1. Splitting the string: TEXTSPLIT(A2, " ") breaks the sentence in A2 into an array of words. If A2 contains "The train arrives in Paris", the output is {"The", "train", "arrives", "in", "Paris"}.
2. Targeting the first letter: LEFT(TEXTSPLIT(A2, " "), 1) extracts the first character of each word, resulting in {"T", "t", "a", "i", "P"}.
3. Verifying capitalization: UPPER(...) turns all those first characters into uppercase: {"T", "T", "A", "I", "P"}.
4. The EXACT test: EXACT(LEFT(...), UPPER(LEFT(...))) compares the original first characters with the uppercase versions. This yields an array of booleans: {TRUE, FALSE, FALSE, FALSE, TRUE}.
5. Filtering the results: The FILTER function uses that boolean array to keep only the words that returned TRUE, resulting in {"The", "Paris"}.

Handling Edge Cases: Numbers and Special Characters

While the basic formula works well for pure text, it contains a logical flaw when processing numbers or punctuation. For instance, if a word begins with a number (like "1st") or a special character (like "$100"), the EXACT comparison will return TRUE because UPPER("1") is still "1", and EXACT("1", "1") is TRUE.

To prevent non-alphabetic characters from being incorrectly identified as capitalized words, we can apply an "alphabet check." We do this by ensuring that the character's uppercase version is different from its lowercase version. Since numbers and symbols do not have case variations, this check filters them out cleanly.

The logic to verify if a character is a letter is:

NOT(EXACT(UPPER(char), LOWER(char)))

The Robust Solution using LET

Using the LET function allows us to define variables, which makes the formula faster to execute and much easier to read:

=LET(
    words, TEXTSPLIT(A2, " "),
    first_chars, LEFT(words, 1),
    is_upper, EXACT(first_chars, UPPER(first_chars)),
    is_letter, NOT(EXACT(UPPER(first_chars), LOWER(first_chars))),
    FILTER(words, is_upper * is_letter, "No matches")
)

In this formula, the multiplication operator (*) acts as an AND logic gate. Only words where both is_upper and is_letter are TRUE will bypass the filter.

Scenario 2: Extracting ONLY Entirely Uppercase Words (Acronyms)

If you only want to extract words that are fully capitalized (like "NASA", "CEO", "UNICEF") while ignoring standard title-case words (like "London" or "John"), you can modify the comparison target.

Instead of comparing just the first character of each word, compare the entire word against its uppercase counterpart:

=LET(
    words, TEXTSPLIT(A2, " "),
    is_all_caps, EXACT(words, UPPER(words)),
    is_letter, NOT(EXACT(UPPER(LEFT(words, 1)), LOWER(LEFT(words, 1)))),
    FILTER(words, is_all_caps * is_letter, "No matches")
)

For example, if the text is "The CEO of IBM lives in New York.", this formula will extract {"CEO", "IBM"}, leaving behind "The", "New", and "York".

Cleaning Up Punctuation

When working with real-world sentences, words often end with punctuation marks (commas, periods, question marks). If your text contains "We visited Paris, France.", the split words will be "Paris," (with a comma) and "France." (with a period). This can skew capitalization checks and leave messy punctuation in your final output.

To solve this, we can scrub common punctuation characters before running the extraction. We can do this using the nested REDUCE and SUBSTITUTE pattern within our LET structure:

=LET(
    raw_text, A2,
    punctuation, {".", ",", "!", "?", ";", ":"},
    clean_text, REDUCE(raw_text, punctuation, LAMBDA(text, char, SUBSTITUTE(text, char, ""))),
    words, TEXTSPLIT(clean_text, " "),
    first_chars, LEFT(words, 1),
    is_upper, EXACT(first_chars, UPPER(first_chars)),
    is_letter, NOT(EXACT(UPPER(first_chars), LOWER(first_chars))),
    FILTER(words, is_upper * is_letter, "")
)

This advanced formula strips out periods, commas, exclamation marks, question marks, semicolons, and colons before splitting the string, ensuring that only clean, punctuation-free words are evaluated and returned.

Displaying the Output in a Single Cell

By default, the FILTER function returns a dynamic array that spills downward or across adjacent cells. If you prefer to have all extracted words compiled inside a single cell, wrap the output in a TEXTJOIN function:

=TEXTJOIN(", ", TRUE, LET(...[insert formula here]...))

For example, wrapping our punctuation-scrubbed formula inside TEXTJOIN yields a clean, comma-separated list of matches within a single cell, as seen in the examples below:

Summary of Benefits

By shifting away from legacy tools to modern Excel functions like TEXTSPLIT and EXACT, you gain several distinct advantages:

• No VBA Required: Your workbooks remain macro-free, making them safer, lighter, and compatible with Excel Online.
• Dynamic Updates: If the source text in cell A2 changes, the extracted word list recalculates instantaneously.
• Scale and Speed: Dynamic array engines execute these calculations faster than manual string-looping scripts.

With these formula patterns in your toolkit, parsing and filtering case-sensitive string data in Excel becomes a streamlined, elegant process.