Text functions

LEFT, RIGHT, MID, TEXTJOIN, TRIM and the newer split functions.

What you'll be able to do

Extract parts of a string
Combine text robustly
Clean text before matching

Extracting

=LEFT(A2, 3)               ' first 3 characters
=RIGHT(A2, 4)              ' last 4
=MID(A2, 5, 3)             ' 3 characters from position 5
=LEN(A2)                   ' length
=FIND("@", A2)             ' position, case-sensitive
=SEARCH("@", A2)           ' position, not case-sensitive, allows wildcards

Combining them extracts a domain:

=MID(A2, FIND("@", A2) + 1, LEN(A2))

Combining

=CONCAT(A2, " ", B2)
=TEXTJOIN(", ", TRUE, A2:A10)

TEXTJOIN is the useful one: a delimiter, whether to ignore empties, then a range. Joining ten cells with commas is one function rather than a chain of ampersands, and the TRUE means blank cells do not leave double commas.

Cleaning

=TRIM(A2)          ' removes leading, trailing and repeated inner spaces
=CLEAN(A2)         ' strips non-printing characters
=UPPER(A2) / =LOWER(A2) / =PROPER(A2)
=SUBSTITUTE(A2, "Ltd", "Limited")

TRIM before every lookup

Trailing whitespace is the single commonest reason a VLOOKUP or XLOOKUP returns #N/A on values that look identical. =TRIM(A2) on both sides fixes it, and it is invisible until you check.

Newer functions

In Microsoft 365:

=TEXTBEFORE(A2, "@")        ' everything before the delimiter
=TEXTAFTER(A2, "@")         ' everything after
=TEXTSPLIT(A2, ",")         ' spills into several cells

These replace most of the LEFT/FIND gymnastics above. Worth knowing both, since older versions and shared workbooks will not have them.

Key points

TEXTJOIN combines a range with a delimiter and skips blanks
TRIM both sides before any lookup — whitespace is invisible
TEXTBEFORE and TEXTAFTER replace most LEFT/FIND combinations

Check yourself