Excel Text Functions

Text functions let you join, cut, clean and change text, which is very useful when data comes from forms or other systems.

Joining Text

Use & or the CONCAT function to join text from several cells:

=A2&" "&B2
=CONCAT(A2, " ", B2)

TEXTJOIN joins a whole range with a separator and can skip empty cells:

=TEXTJOIN(", ", TRUE, A2:A5)

Changing Case

=UPPER(A2)
=LOWER(A2)
=PROPER(A2)

PROPER is great for fixing names typed in all capitals or all small letters.

Cleaning Extra Spaces

Data from forms often has extra spaces that you cannot see but that break lookups and comparisons. TRIM removes spaces at the start and end and turns double spaces into single ones:

=TRIM(A2)
=LEN(A2)
=LEN(TRIM(A2))

LEN counts characters, including spaces. Comparing LEN before and after TRIM shows if a cell had extra spaces.

Cutting Out Part of the Text

Student roll numbers look like KHI-2026-0147:

=LEFT(A2, 3)
=MID(A2, 5, 4)
=RIGHT(A2, 4)

Finding Text

FIND gives the position of some text inside another text. Combined with LEFT, it can take the first name from a full name, whatever its length:

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

FIND is case sensitive; SEARCH works the same way but ignores case.

Replacing Text

SUBSTITUTE replaces some text with other text. Remove dashes from phone numbers:

=SUBSTITUTE(A2, "-", "")

Formatting Numbers as Text

TEXT turns a number or date into text in the format you choose, so you can join it into a sentence:

="Total: Rs "&TEXT(B2, "#,##0")
="Date: "&TEXT(C2, "dd-mmm-yyyy")

Flash Fill

For simple patterns you do not even need a formula. Type the result you want for the first row (for example the first name), go to the next cell and press Ctrl + E. Excel's Flash Fill guesses the pattern and fills the rest of the column. Check the result, because it can guess wrong.

Exercise