Text functions · Excel

Imported data is often messy: extra spaces, inconsistent capitals, several facts crammed into one cell. Text functions fix it.

Cleaning up

	A	B
1	Raw name	Clean
2	`asha VERMA`	`=PROPER(TRIM(A2))`

Excel formula

=TRIM(A2)            → "asha VERMA"     removes spaces at both ends, and doubles inside
=PROPER(TRIM(A2))    → "Asha Verma"     capitalises each word
=UPPER("leo")        → "LEO"
=LOWER("LEO")        → "leo"

TRIM is the first thing to try when a lookup returns #N/A for a value you can see in the list: an invisible space at the end is a common culprit.

Taking parts of text

Suppose A2 contains the product code IN-MUM-00731:

Excel formula

=LEFT(A2, 2)         → "IN"       the first 2 characters
=RIGHT(A2, 5)        → "00731"    the last 5
=MID(A2, 4, 3)       → "MUM"      3 characters, starting at character 4
=LEN(A2)             → 12         the number of characters

Characters are counted from 1.

Finding a position

Excel formula

=FIND("@", "asha@example.com")        → 5
=SEARCH("v", "Asha Verma")            → 6
=FIND("v", "Asha Verma")              → #VALUE!   FIND is case-sensitive: there's no lower-case v

FIND and SEARCH both return the position of one piece of text inside another. SEARCH ignores case and accepts the wildcards * and ?; FIND is case-sensitive. Both give #VALUE! when there's no match.

They're most useful combined with LEFT, MID and RIGHT, to split text at a character. With an email address in B2:

Excel formula

=LEFT(B2, FIND("@", B2) - 1)          → "asha"           everything before the @
=MID(B2, FIND("@", B2) + 1, 100)      → "example.com"    everything after it

And with a full name in A2:

Excel formula

=LEFT(A2, FIND(" ", A2) - 1)               → "Asha"     first name
=RIGHT(A2, LEN(A2) - FIND(" ", A2))        → "Verma"    last name

Replacing text

Excel formula

=SUBSTITUTE("2024-06-03", "-", "/")       → "2024/06/03"
=SUBSTITUTE("a-b-c", "-", "", 1)          → "ab-c"     only the first occurrence

SUBSTITUTE(text, old, new) replaces every occurrence, or only the nth one if you give a fourth argument.

Joining text

Excel formula

="Asha" & " " & "Verma"                    → "Asha Verma"
=A2 & " (" & B2 & ")"                      → "Asha Verma (asha@example.com)"
=CONCAT(A2:A4)                             → the three cells run together
=TEXTJOIN(", ", TRUE, A2:A4)               → "Asha, Leo, Mei"

& joins any pieces of text. TEXTJOIN(separator, ignore_empty, range) joins a whole range with a separator between items, skipping empty cells when the second argument is TRUE.

Numbers as formatted text: TEXT

Excel formula

="Total: " & 1234.5                       → "Total: 1234.5"
="Total: " & TEXT(1234.5, "#,##0.00")     → "Total: 1,234.50"
=TEXT(0.18, "0%")                         → "18%"
=TEXT(DATE(2024,7,1), "dddd")             → "Monday"

Joining a number into text loses its formatting. TEXT(value, format_code) applies a number format first, using the same codes as custom formats. The opposite, VALUE("42"), turns text that looks like a number back into a number.

Newer functions

Microsoft 365 adds simpler ways to split text:

Excel formula

=TEXTBEFORE("asha@example.com", "@")      → "asha"
=TEXTAFTER("asha@example.com", "@")       → "example.com"
=TEXTSPLIT("Asha,Leo,Mei", ",")           → Asha | Leo | Mei, across three cells

For one-off clean-ups, also try Data → Flash Fill (Ctrl+E): type the result you want for the first row or two, and Excel works out the pattern for the rest.