Excel's text functions clean messy data, split/combine text, and extract specific portions. Essential for data preparation and formatting.

Combining Text

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

Extracting Text

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!

Cleaning Text

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

Case Conversion

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")

Finding & Replacing

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")

Text Length & Repetition

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: "███████░░░"

Excel 365 Power Functions

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

Practical Examples

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),"")))

Real-World Cleanup Tasks

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), " ")

Text to Columns Alternative

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))