Text Functions: Clean and Transform Data Like a Pro

Excel's text functions combine, extract, clean, and reformat text in cells — TEXTJOIN and & combine values, LEFT/RIGHT/MID and TEXTSPLIT extract pieces, and TRIM/CLEAN/SUBSTITUTE fix messy imported data.

How do I combine text from multiple cells?

Use the & operator for a quick concatenation, CONCAT for a slightly more readable version, or TEXTJOIN when you need a delimiter and want to skip blank cells automatically.

Concatenation (Modern Way)

& operator (simplest):
=A2 & " " & B2
"John" & " " & "Smith" → "John Smith"

CONCAT (Excel 2016+):
=CONCAT(A2, " ", B2)

TEXTJOIN (Best for lists - Excel 2019+):
=TEXTJOIN(", ", TRUE, A2:A10)
Joins A2:A10 with comma-space separator
TRUE = ignore empty cells

Example: "Apple, Orange, Banana"

Combining with Line Breaks

=A2 & CHAR(10) & B2 & CHAR(10) & C2

CHAR(10) = line break
Enable Wrap Text to see multiple lines

Result:
Line 1
Line 2
Line 3

How do I extract part of a text string in Excel?

LEFT, RIGHT, and MID pull a fixed or FIND-calculated number of characters from a string; Excel 365's TEXTSPLIT, TEXTBEFORE, and TEXTAFTER do the same job with less nested formula logic.

LEFT, RIGHT, MID

LEFT(text, num_chars) - Extract from start:
=LEFT(A2, 3)
"Excel123" → "Exc"

RIGHT(text, num_chars) - Extract from end:
=RIGHT(A2, 3)
"Excel123" → "123"

MID(text, start_position, num_chars) - Extract from middle:
=MID(A2, 4, 3)
"Excel123" → "el1"
Starts at position 4, takes 3 characters

Extracting Before/After a Character

Before the @ in email:
=LEFT(A2, FIND("@", A2) - 1)
"[email protected]" → "john.smith"

After the @:
=MID(A2, FIND("@", A2) + 1, 999)
"[email protected]" → "company.com"

Better way (Excel 365):
=TEXTSPLIT(A2, "@")
Returns both parts automatically!

First/Last Name Split

First name:
=LEFT(A2, FIND(" ", A2) - 1)

Last name:
=MID(A2, FIND(" ", A2) + 1, 999)

Excel 365 (much easier):
=TEXTSPLIT(A2, " ")
Automatically creates 2 columns!

How do I clean up messy imported text in Excel?

TRIM removes extra spaces, CLEAN strips non-printable characters left over from web or PDF copy-paste, and SUBSTITUTE swaps out specific characters or words.

TRIM - Remove Extra Spaces

=TRIM(A2)

"  John   Smith  " → "John Smith"

Removes:
- Leading spaces
- Trailing spaces
- Extra spaces between words (leaves only 1)

Essential for cleaning imported data!

CLEAN - Remove Non-Printable Characters

=CLEAN(A2)

Removes line breaks, tabs, special characters
Common when copying from web or PDFs

Often combine:
=TRIM(CLEAN(A2))

SUBSTITUTE - Find and Replace

=SUBSTITUTE(text, old_text, new_text, [instance])

Remove hyphens:
=SUBSTITUTE(A2, "-", "")
"123-456-7890" → "1234567890"

Replace first occurrence only:
=SUBSTITUTE(A2, "apple", "orange", 1)

Replace all:
=SUBSTITUTE(A2, "apple", "orange")

Multiple replacements:
=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "")
Removes both hyphens and spaces

How do I change text to uppercase, lowercase, or title case?

UPPER, LOWER, and PROPER convert case directly, but PROPER capitalizes every word literally — including names like "McDonald" — so check the output on real data.

UPPER(text) - All caps:
=UPPER(A2)
"john smith" → "JOHN SMITH"

LOWER(text) - All lowercase:
=LOWER(A2)
"JOHN SMITH" → "john smith"

PROPER(text) - Title case:
=PROPER(A2)
"john smith" → "John Smith"

Gotcha: PROPER("mcdonald") → "Mcdonald" (not "McDonald")

What's the difference between FIND and SEARCH in Excel?

FIND is case-sensitive and doesn't support wildcards; SEARCH is case-insensitive and does support wildcards like * and ? — otherwise the two work identically.

FIND vs SEARCH

FIND - Case sensitive:
=FIND("Excel", A2)
Returns position of "Excel" (must match case exactly)

SEARCH - Case insensitive:
=SEARCH("excel", A2)
Finds "Excel", "EXCEL", "excel"

Both support wildcards:
=SEARCH("*@gmail.com", A2)
Returns position if found, #VALUE! if not found

Check if Text Contains Something

=ISNUMBER(SEARCH("gmail", A2))

Returns TRUE if "gmail" appears anywhere
Returns FALSE if not found

Or with IF:
=IF(ISNUMBER(SEARCH("gmail", A2)), "Personal", "Work")

How do I count characters or repeat text in Excel?

LEN counts every character in a string, and REPT repeats a character or string a set number of times — useful for building simple text-based progress bars.

LEN(text) - Count characters:
=LEN(A2)
"Excel" → 5

REPT(text, times) - Repeat text:
=REPT("*", 5)
→ "*****"

Create progress bar:
=REPT("█", A2/10) & REPT("░", 10-A2/10)
If A2=70: "███████░░░"

What text functions are new in Excel 365?

TEXTSPLIT, TEXTBEFORE, and TEXTAFTER replace multi-step LEFT/RIGHT/FIND formulas with a single function call, and spill their results across cells automatically.

TEXTSPLIT (Replaces Text to Columns)

=TEXTSPLIT(A2, ",")

"Apple,Orange,Banana" → 3 columns
Automatically spills across cells!

Multiple delimiters:
=TEXTSPLIT(A2, {",", ";", "|"})

Split by rows and columns:
=TEXTSPLIT(A2, ",", CHAR(10))
Splits by comma (columns) and line break (rows)

TEXTBEFORE & TEXTAFTER

=TEXTBEFORE(A2, "@")
"[email protected]" → "john"

=TEXTAFTER(A2, "@")
"[email protected]" → "company.com"

Much simpler than LEFT/RIGHT/FIND combinations!

Get last word:
=TEXTAFTER(A2, " ", -1)
-1 = from the end

How do text functions combine in real formulas?

Extracting an email domain, formatting a phone number, or generating a company email address all chain two or three text functions together.

Extract Domain from Email

=MID(A2,
    FIND("@", A2) + 1,
    FIND(".", A2, FIND("@", A2)) - FIND("@", A2) - 1
)

"[email protected]" → "company"

Or Excel 365:
=TEXTBEFORE(TEXTAFTER(A2, "@"), ".")

Format Phone Numbers

From "1234567890" to "(123) 456-7890":

="(" & LEFT(A2,3) & ") " & MID(A2,4,3) & "-" & RIGHT(A2,4)

Create Email from Name

=LOWER(
    SUBSTITUTE(A2, " ", ".") & "@company.com"
)

"John Smith" → "[email protected]"

Or:
=LOWER(LEFT(A2, FIND(" ", A2)-1) & "." &
    MID(A2, FIND(" ", A2)+1, 99) & "@company.com")

Extract Numbers from Text

Excel 365:
=VALUE(CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),
    MID(A2,ROW($1:$99),1),"")))

"Order #12345 shipped" → 12345

Simpler with newer functions:
=NUMBERVALUE(TEXTJOIN("",TRUE,
    IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),
    MID(A2,SEQUENCE(LEN(A2)),1),"")))

What's the right text function for common cleanup problems?

Most data-cleanup requests map to one function each — the table below is a quick lookup for the usual offenders.

Problem	Solution
Extra spaces	=TRIM(A2)
Wrong case	=PROPER(A2) or =UPPER(A2)
Special characters	=SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "@", "")
Leading zeros lost	=TEXT(A2, "00000")
Line breaks in cell	=SUBSTITUTE(A2, CHAR(10), " ")

Is there a formula alternative to Text to Columns?

Yes — Text to Columns overwrites your original data permanently, so a formula-based split (TEXTSPLIT in Excel 365, or nested SUBSTITUTE/LEFT/MID in older versions) is safer when you want to keep the source intact.

Old way: Data → Text to Columns (destroys original data)

Formula way (reversible):

Excel 365:
=TEXTSPLIT(A2, ",")

Older Excel:
Column B: =TRIM(LEFT(SUBSTITUTE(A2,",",REPT(" ",999)),999))
Column C: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),999,999))
Column D: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",999)),999))