Course contents · Excel functions

Text functions

Open the practice in the trainer →

Data from CRMs and exports is almost always "dirty": extra spaces, phone numbers in different formats. Text functions clean it up.

TRIM(text)removes spaces at the ends and double spaces inside
SUBSTITUTE(text, old, new)replaces a fragment; "" deletes it. For several characters, nest the functions
LEFT(text, n)the first n characters (see also RIGHT, MID)
SEARCH(find, within)position of a fragment, case-insensitive; #VALUE! if not found
TEXTJOIN(delimiter, ignore_empty, …)joins values with a delimiter; by hand: =A2&", "&B2

Tricks: =ISNUMBER(SEARCH("urgent", C2)) — does the text contain a word (TRUE/FALSE); =LEFT(A2, SEARCH(" ", A2)-1) — the first word.

Tasks in the trainer

  1. In D2:D5 remove extra spaces from customer names (they are at the ends and between words).
  2. In E2:E5 keep only the digits of the phone: remove spaces, parentheses and hyphens.
  3. In F2:F5 extract the first name — everything before the first space in the cleaned name (column D).
  4. In G2:G5 check whether the comment contains the word "urgent" in any case (TRUE/FALSE).
  5. In H2:H5 build a card "Customer, phone digits" from D and E, separated by a comma and a space.

Solve the tasks in the trainer →