Excel Text Functions: Clean, Split, Join, and Extract

Excel text functions turn inconsistent labels, imported records, product codes, and names into usable data. The key is to choose the function by the job: clean the source, locate a marker, extract the required piece, replace unwanted content, or combine fields into a readable result.

This guide covers both modern functions such as TEXTSPLIT, TEXTBEFORE, and TEXTAFTER and compatible methods built from LEFT, MID, RIGHT, and FIND. You will also learn why some apparently correct formulas fail.

The formulas below use commas between arguments. Excel may require semicolons in some regional settings. Follow the separator shown by Formula AutoComplete in your installation.

Choose an Excel text function by task

Microsoft’s text functions reference contains the full function family. The sections below focus on a practical workflow and the choices that most often affect the result.

Clean text before extracting from it

Cleaning first makes later formulas more reliable. A delimiter in the expected position is not useful if invisible characters or repeated spaces change the source.

TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. CLEAN removes the first 32 nonprinting characters in the ASCII character set. For imported content in A2, combine them like this:

=TRIM(CLEAN(A2))

This example uses =TRIM(CLEAN(A2)) to remove extra spaces and nonprinting characters from messy text and return a clean result.

This formula is a useful first pass, but it is not a universal character scrubber. Microsoft notes that TRIM does not remove the nonbreaking space character, and CLEAN does not remove every Unicode nonprinting character. If a stubborn space remains, identify its source before applying a broader replacement.

To standardize capitalization, use:

=UPPER(A2)
=LOWER(A2)
=PROPER(A2)

UPPER and LOWER are predictable for codes and normalized comparisons. PROPER capitalizes words, which is convenient for many names and labels, but review acronyms and brand styling after the conversion.

Extract fixed-position text with LEFT, MID, RIGHT, and LEN

Suppose A2 contains the product code INV-2026-0048. Its segments always occupy the same positions. Fixed-position functions are therefore the simplest choice:

This example extracts fixed-position text from a product code with LEFT, MID, and RIGHT, and counts all characters with LEN.

LEFT, MID, and RIGHT return text, even when the extracted characters look like a number. That is often correct for identifiers: converting 0048 to a number would discard its leading zeros. LEN, by contrast, returns a numeric character count.

Fixed positions work only while the structure remains fixed. If prefixes can have different lengths or delimiters can move, locate the delimiter instead of hard-coding a character number.

Locate text with FIND or SEARCH

FIND and SEARCH return the position where a character or substring begins. The important difference is case sensitivity:

• FIND is case-sensitive and does not support wildcard characters.
• SEARCH is not case-sensitive and supports wildcard characters.

For INV-2026-0048 in A2, this returns 4, the position of the first hyphen:

=FIND("-",A2)

For a description in B2, the next formula finds mug, Mug, or MUG:

=SEARCH("mug",B2)

This example uses FIND to return the position of the first hyphen in a code and SEARCH to find the word "mug" in a text string without case sensitivity.

If the requested text is absent, both functions return #VALUE!. That error is useful when a missing delimiter signals bad source data. If absence is expected, handle it deliberately:

=IFERROR(SEARCH("mug",B2),0)

The formula returns 0 when there is no match. Microsoft’s FIND and SEARCH error guide documents the case behavior and common reasons for #VALUE!.

Split delimited text with modern functions

Assume A2 contains:

NW-104 | Ceramic Mug | Blue

In Microsoft 365 and Excel 2024, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT make delimiter-based extraction concise. Because the example has spaces around each vertical bar, wrapping the result in TRIM removes the extra spaces.

A negative instance number in TEXTAFTER counts delimiters from the end, so -1 returns the content after the last vertical bar. Microsoft provides the optional arguments and matching behavior in its references for TEXTBEFORE and TEXTAFTER.

TEXTSPLIT returns a dynamic array. The results occupy neighboring cells and update when the source changes. The spill area must be empty, or Excel returns #SPILL!. You can also supply a row delimiter to spill results downward; see Microsoft’s TEXTSPLIT reference for the complete syntax.

If Excel returns #NAME? for a modern text function, check your Excel version. For older versions, use LEFT, MID, or RIGHT with FIND/SEARCH, or use the desktop Text to Columns command for a one-time split.

Build a compatible delimiter formula

Older Excel versions can extract around a delimiter by combining functions. If A2 contains NW-104 | Ceramic Mug | Blue, this returns everything before the first vertical bar and removes the adjacent space:

=TRIM(LEFT(A2,FIND("|",A2)-1))

This formula combines FIND, LEFT, and TRIM to return the text before the first delimiter without requiring newer Excel text functions.

The calculation works from the inside out:

1. Find the marker. FIND("|",A2) returns the position of the first vertical bar.
2. Exclude the marker. Subtracting 1 gives the number of characters before it.
3. Extract the left portion. LEFT returns that many characters.
4. Remove the remaining space. TRIM cleans the result.

This method is dependable when the delimiter exists exactly where the formula expects it. If some rows omit the delimiter, decide whether to flag those rows for correction or wrap the formula in IFERROR with a meaningful fallback.

Replace content with SUBSTITUTE or REPLACE

The two replacement functions answer different questions:

• SUBSTITUTE asks, “Which matching text should change?”
• REPLACE asks, “Which character positions should change?”

To change every hyphen in INV-2026-0048 to a slash, use:

=SUBSTITUTE(A2,"-","/")

To change only the second occurrence, use the optional instance number:

=SUBSTITUTE(A2,"-","/",2)

To replace the four characters beginning at position 5 with a new year, use:

=REPLACE(A2,5,4,"2027")

This example shows how SUBSTITUTE replaces matching text, while REPLACE changes characters based on their position in the string.

The result is INV-2027-0048. Use SUBSTITUTE when you know the content to match; use REPLACE when the positions and length are stable. Microsoft documents the distinction in its SUBSTITUTE and REPLACE references.

Join text with an operator, CONCAT, or TEXTJOIN

The ampersand operator is ideal for a short, explicit formula. If B2 contains a first name and C2 contains a last name, use:

=B2&" "&C2

CONCAT combines text or ranges without adding a delimiter:

=CONCAT(B2:D2)

TEXTJOIN is better when every item needs the same separator or blank cells should be ignored:

=TEXTJOIN(", ",TRUE,B2:D2)

This example shows three ways to combine text in Excel: the ampersand operator for simple joins, CONCAT for combining ranges, and TEXTJOIN for joining values with a delimiter.

Here, ", " is inserted between items and TRUE tells Excel to ignore empty cells. Microsoft’s TEXTJOIN reference explains the syntax and limits.

CONCATENATE remains available for compatibility, but Microsoft replaced it with CONCAT in newer Excel versions and recommends using CONCAT going forward. Existing CONCATENATE formulas do not need to be rewritten merely to keep working.

Preserve number and date formatting inside text

When a number is joined to text, its cell formatting does not automatically travel with it. Use TEXT to control the displayed result. If B2 contains 48, this creates a six-digit invoice label:

="Invoice "&TEXT(B2,"000000")

This example compares a plain text join with a TEXT-based formula that formats the number 48 as 000048 inside an invoice label.

The result is Invoice 000048. A similar formula can format a valid date in C2:

="Due "&TEXT(C2,"mmmm d, yyyy")

TEXT converts its result to text. Keep the original numeric or date cell when you still need it for arithmetic, sorting, or date calculations. Microsoft makes the same distinction in its TEXT function guidance.

Convert numeric text when calculation is the goal

An imported value can look like a number while remaining text. VALUE converts recognized numeric text into a number:

=VALUE(A2)

The interpretation follows recognized number, date, or time formats and can depend on regional settings. When the source explicitly uses known decimal and group separators, NUMBERVALUE gives you more control. For example, this treats a period as the decimal separator and a comma as the group separator:

=NUMBERVALUE(A2,".",",")

Do not convert identifiers merely because they contain digits. Postal codes, account codes, and product IDs often need to remain text so that leading zeros and exact characters are preserved.

A repeatable workflow for messy text

For a new import, work through the transformation in small, testable stages:

1. Keep the raw column. Make changes in helper columns so you can compare the output with the source.
2. Inspect patterns and exceptions. Check spaces, delimiters, letter case, missing values, and whether codes have fixed lengths.
3. Clean first. Use TRIM, CLEAN, or a targeted SUBSTITUTE only where the source requires it.
4. Choose a parsing method. Use fixed-position functions for fixed structures and delimiter functions for variable-length fields.
5. Test edge cases. Include rows with missing delimiters, empty fields, extra delimiters, different capitalization, and unusually long values.
6. Confirm the output type. Keep IDs as text; convert quantities and amounts to numbers only when calculation is intended.
7. Paste values only when appropriate. If the cleaned output must stop depending on the source, copy it and use Paste Special > Values after verification.

Troubleshoot common text-formula problems

Frequently asked questions

Build formulas around the structure of the data

There is no single “best” Excel text function. Use fixed-position functions when the length never changes, delimiter functions when separators define the fields, and cleaning functions before either method when the source is inconsistent. Modern functions reduce formula complexity, while the older building blocks remain valuable for compatibility and custom logic.

The safest approach is to preserve the source, transform it in visible stages, and test rows that break the normal pattern. That produces formulas you can explain, audit, and maintain when the next data export arrives.