Excel Text Functions: Complete Guide to LEFT, MID, RIGHT, LEN, TRIM, SUBSTITUTE and More

Excel text functions let you extract, clean, combine, and transform text data without manual editing. Whether you are splitting names from a full name column, stripping extra spaces from imported data, or reformatting codes, these functions are the tools for the job.

LEFT — Extract Characters from the Left

LEFT returns a specified number of characters from the beginning (left side) of a text string. Syntax: =LEFT(text, num_chars) Example:

=LEFT("Hello World", 5) returns "Hello"
=LEFT(A1, 3) returns the first 3 characters of whatever is in A1

Practical use: Extract the area code from a phone number stored as text. If A1 contains "212-555-0100", =LEFT(A1, 3) returns "212".

RIGHT — Extract Characters from the Right

RIGHT returns a specified number of characters from the end (right side) of a text string. Syntax: =RIGHT(text, num_chars) Example:

=RIGHT("ABC-12345", 5) returns "12345"
=RIGHT(A1, 4) returns the last 4 characters

Practical use: Extract file extensions. =RIGHT(A1, 3) on "report.pdf" returns "pdf". For variable-length extensions, combine with FIND: =RIGHT(A1, LEN(A1) - FIND(".", A1)).

MID — Extract Characters from the Middle

MID returns a substring from the middle of a text string, starting at a specified position. Syntax: =MID(text, startnum, numchars)

start_num — character position to start from (1 = first character)
num_chars — how many characters to return

Example:

=MID("Hello World", 7, 5) returns "World" (starts at position 7, takes 5 characters)
=MID("2024-06-15", 6, 2) returns "06" (the month)

Practical use: Extract a middle section of a product code. If A1 is "AB-1234-XY", =MID(A1, 4, 4) returns "1234".

LEN — Count Characters

LEN returns the total number of characters in a text string, including spaces. Syntax: =LEN(text) Example:

=LEN("Hello") returns 5
=LEN("Hello World") returns 11 (the space counts)
=LEN(A1) counts characters in A1

Practical use: Validate that entries are the right length. =IF(LEN(A1)<>10, "Wrong length", "OK") flags entries in A1 that are not exactly 10 characters. Also used with MID and RIGHT to handle variable-length strings.

TRIM — Remove Extra Spaces

TRIM removes all leading and trailing spaces from text, and reduces multiple consecutive spaces between words to a single space. Syntax: =TRIM(text) Example:

=TRIM(" Hello World ") returns "Hello World"

Why this matters: Data imported from other systems often contains invisible leading or trailing spaces. These spaces make lookups fail — VLOOKUP or MATCH will not find "Apple " (with a trailing space) when looking for "Apple". Always TRIM imported text before using it in lookups. TRIM does not remove non-breaking spaces (character code 160). To remove those, use =TRIM(SUBSTITUTE(A1, CHAR(160), " ")).

UPPER, LOWER, PROPER — Change Text Case

These three functions change the capitalization of text.

Syntax:

=UPPER(text) — converts all characters to uppercase
=LOWER(text) — converts all characters to lowercase
=PROPER(text) — capitalizes the first letter of each word

Examples:

=UPPER("hello world") returns "HELLO WORLD"
=LOWER("JOHN SMITH") returns "john smith"
=PROPER("john smith") returns "John Smith"

Practical use: Normalize names or codes before comparisons. PROPER is handy for fixing all-caps imports of names, but watch out — it also capitalizes after apostrophes, so "O'BRIEN" becomes "O'Brien" correctly, but "IT'S" becomes "It'S".

SUBSTITUTE — Replace Text

SUBSTITUTE replaces every occurrence of a specified text string within a larger string. Syntax: =SUBSTITUTE(text, oldtext, newtext, [instance_num])

instance_num — optional. If provided, replaces only that specific occurrence (1 = first, 2 = second, etc.). If omitted, replaces all occurrences.

Examples:

=SUBSTITUTE("Hello World", "World", "Excel") returns "Hello Excel"
=SUBSTITUTE("aaa", "a", "b") returns "bbb"
=SUBSTITUTE("a-b-c", "-", "", 2) returns "a-bc" (removes only the second hyphen)

Practical use: Remove unwanted characters. =SUBSTITUTE(A1, " ", "") removes all spaces. =SUBSTITUTE(A1, "$", "") strips dollar signs before converting text to numbers.

REPLACE — Replace by Position

REPLACE replaces characters at a specified position, regardless of content. Use it when you know where in the string the replacement should happen, not what the text is. Syntax: =REPLACE(oldtext, startnum, numchars, newtext) Example:

=REPLACE("ABC-12345", 4, 1, "/") returns "ABC/12345" (replaces the hyphen at position 4 with a slash)
=REPLACE(A1, 1, 3, "XYZ") replaces the first 3 characters with "XYZ"

SUBSTITUTE vs. REPLACE:

Use SUBSTITUTE when you know the text to replace: SUBSTITUTE(A1, "old", "new")
Use REPLACE when you know the position: REPLACE(A1, 5, 3, "new")

FIND — Find Position (Case-Sensitive)

FIND returns the character position of a substring within a larger string. It is case-sensitive. Syntax: =FIND(findtext, withintext, [start_num])

start_num — optional, the position to start searching from (default is 1)

Examples:

=FIND("l", "Hello") returns 3 (position of first "l")
=FIND("L", "Hello") returns #VALUE! — uppercase L is not found because FIND is case-sensitive
=FIND("@", "[email protected]") returns 5

Practical use: FIND is commonly nested inside LEFT or MID to extract text up to a delimiter. To extract everything before the "@" in an email: =LEFT(A1, FIND("@", A1) - 1).

SEARCH — Find Position (Case-Insensitive)

SEARCH works exactly like FIND but ignores case. It also supports wildcards (* and ?). Syntax: =SEARCH(findtext, withintext, [start_num]) Examples:

=SEARCH("hello", "Hello World") returns 1 (finds it despite case difference)
=SEARCH("h?llo", "Hello") returns 1 (wildcard matches any single character)

Use SEARCH when you want case-insensitive position finding, or when you need wildcard matching.

CONCAT and TEXTJOIN — Combine Text

CONCAT

CONCAT joins multiple text strings or ranges into one. Syntax: =CONCAT(text1, text2, ...) Example:

=CONCAT(A1, " ", B1) joins first name, a space, and last name
=CONCAT(A1:A5) joins all values in A1:A5 with no separator

TEXTJOIN

TEXTJOIN joins values with a delimiter and can optionally skip empty cells. Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...) Examples:

=TEXTJOIN(", ", TRUE, A1:A5) joins A1:A5 with commas, skipping empty cells
=TEXTJOIN("-", FALSE, "2024", "06", "15") returns "2024-06-15"

TEXTJOIN is more flexible than CONCAT when you need separators or want to handle blanks gracefully.

TEXT — Convert Numbers to Formatted Text

TEXT converts a number or date to a text string with a specific format. This is how you display numbers as text inside sentences or concatenations. Syntax: =TEXT(value, format_text) Examples:

=TEXT(1234.5, "$#,##0.00") returns "$1,234.50"
=TEXT(TODAY(), "MMMM D, YYYY") returns "April 16, 2026"
=TEXT(0.856, "0%") returns "86%"
=TEXT(A1, "000000") pads a number with leading zeros to 6 digits

Practical use: Build dynamic text strings with properly formatted numbers:

="Revenue was " & TEXT(A1, "$#,##0") & " in Q" & B1

Returns: "Revenue was $1,234,500 in Q3"

Without TEXT, the number would appear unformatted in the string.

Combining Functions: Real-World Examples

Extract First Name from Full Name

Full name in A1, space-separated (e.g., "John Smith"):

=LEFT(A1, FIND(" ", A1) - 1)

FIND locates the space. LEFT takes everything before it.

Extract Last Name from Full Name

=RIGHT(A1, LEN(A1) - FIND(" ", A1))

FIND locates the space position. LEN gives total length. Subtracting gives the number of characters after the space. RIGHT extracts them.

For names with middle names or multiple spaces, use a more complex formula with SUBSTITUTE and FIND to locate the last space.

Extract Domain from Email Address

Email in A1 (e.g., "[email protected]"):

=RIGHT(A1, LEN(A1) - FIND("@", A1))

FIND finds the "@" position. LEN minus that position gives the number of characters after "@". RIGHT extracts them: "example.com".

Extract Username from Email

=LEFT(A1, FIND("@", A1) - 1)

Returns everything before the "@": "user".

Clean Imported Data (Trim + Proper)

=PROPER(TRIM(A1))

Removes extra spaces and properly capitalizes names in one step.

Remove Non-Numeric Characters from a Phone Number

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","")

Nested SUBSTITUTE calls strip parentheses, hyphens, and spaces, leaving only digits.

Frequently Asked Questions

What is the difference between FIND and SEARCH?

Both return the position of a substring. FIND is case-sensitive — FIND("a", "Apple") returns #VALUE! because there is no lowercase "a". SEARCH is case-insensitive — SEARCH("a", "Apple") returns 1. SEARCH also supports wildcards (* for any sequence of characters, ? for any single character). For most lookups, SEARCH is more forgiving.

Why does TRIM not remove all spaces?

TRIM removes regular space characters (ASCII 32) and reduces internal spaces to one. It does not remove non-breaking spaces (character 160), which often appear in data copied from web pages. Use =SUBSTITUTE(TRIM(A1), CHAR(160), " ") to handle both types, or =CLEAN(TRIM(A1)) to additionally remove non-printable characters.

How do I count words in a cell?

Count spaces and add one: =LEN(TRIM(A1)) - LEN(SUBSTITUTE(TRIM(A1), " ", "")) + 1. TRIM first to normalize spacing, then count how many spaces are left.

Can I extract text between two characters?

Yes, using MID and FIND. To extract text between parentheses in "Result (Good)":

=MID(A1, FIND("(", A1)+1, FIND(")", A1)-FIND("(", A1)-1)

Returns "Good".

How do I check if a cell contains specific text?

Use SEARCH or FIND wrapped in ISNUMBER: =ISNUMBER(SEARCH("apple", A1)) returns TRUE if A1 contains "apple" (case-insensitive). Use this inside an IF: =IF(ISNUMBER(SEARCH("apple", A1)), "Contains apple", "Does not").

What is the maximum text length that Excel can hold in a cell?

A single cell can hold up to 32,767 characters, but only the first 1,024 characters are displayed in the cell. All 32,767 are stored and can be processed by text functions.