UPPER, LOWER, LEN, CONCAT, and TEXTJOIN

Case, length, and joining text on MO-210

The second half of Format and modify text on Exam MO-210 focuses on normalizing case, measuring length, and building combined strings. You will use UPPER, LOWER, LEN, CONCAT, and TEXTJOIN. Projects may ask you to standardize product names, build display labels from first and last names, or assemble addresses from separate city, state, and ZIP columns. As with extraction functions, graders evaluate the formula, so the right function with the right arguments matters more than a one-off typed result.

UPPER and LOWER — standardize letter case

Syntax:

• UPPER(text) — returns the string with all letters converted to uppercase.
• LOWER(text) — returns the string with all letters converted to lowercase.

Digits, spaces, and most punctuation are unchanged. Only A–Z / a–z style letters flip case.

Worked example: normalize a status column

Use UPPER when a task wants codes or statuses in all caps for matching or display. Use LOWER when you need a consistent lowercase key. MO-210 may also ask you to fill a helper column with =UPPER(A2) so later filters or lookups see uniform values.

Case does not trim spaces

=UPPER(" open ") returns OPEN with the same leading and trailing spaces. Case functions do not remove spaces; they only change letter case. If a prompt mentions cleaning spaces, that is a different skill—do not assume UPPER/LOWER will tidy the string.

LEN — count characters

Syntax: LEN(text)

LEN returns how many characters are in the string, including letters, digits, spaces, and symbols.

Worked example: validate ID length

Row 4 includes a space, so the length is 9, not 8. On the exam, LEN is useful when a task asks you to show how many characters are in each cell, or when you combine LEN with LEFT/RIGHT (for example =RIGHT(A2,LEN(A2)-3) in more advanced sheets). Associate-level prompts more often ask for a straight =LEN(A2) in a helper column.

Empty cells and LEN

=LEN(A2) when A2 is truly empty returns 0. A cell that looks blank but contains a space returns 1. That distinction matters if you are checking whether a field was filled.

CONCAT — join values in order

Syntax: CONCAT(text1, [text2], …)

CONCAT stitches its arguments together left to right with no delimiter unless you supply one yourself as a text argument.

Worked example: full name from parts

Notice the explicit " " between first and last. =CONCAT(A2,B2) would return AvaChen with no space—a common mistake when candidates forget the separator.

Worked example: build a product label

You can concatenate cell references, typed strings, and results of other formulas. For example =CONCAT(UPPER(A2),"-",B2) forces the category to uppercase before joining.

TEXTJOIN — join with a delimiter and optional blanks skip

Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• delimiter — the character(s) inserted between values (comma, space, hyphen, and so on).
• ignore_empty — TRUE skips blank arguments/cells; FALSE still inserts delimiters for blanks (which can create double commas or trailing separators).
• text1, … — individual cells, ranges, or strings to join.

Worked example: city, state, ZIP

With ignore_empty set to TRUE, the missing ZIP in row 3 does not leave a dangling comma. If you used FALSE instead, row 3 could become Denver, CO, with an extra trailing delimiter pattern depending on how blanks are treated—on MO-210, when the prompt says to ignore empty cells, choose TRUE.

Worked example: join a range with hyphens

Cells A2:C2 hold NW, 12, and 0845. Formula:

=TEXTJOIN("-",TRUE,A2:C2) → NW-12-0845

TEXTJOIN accepts a range as one argument, which is often cleaner than listing every cell. CONCAT can also accept ranges in Microsoft 365, but it still will not insert delimiters between range items—you would get NW120845 unless you add separators another way. When the task wants separators between many fields, TEXTJOIN is the direct tool.

CONCAT vs TEXTJOIN vs legacy CONCATENATE

Brief contrast for the exam: CONCATENATE(A2," ",B2) and CONCAT(A2," ",B2) produce the same Ava Chen style result. Prefer CONCAT in new formulas unless a legacy workbook already uses CONCATENATE. Choose TEXTJOIN when you need one delimiter repeated between many values or when you must skip blanks with ignore_empty.

Side-by-side on the same data

First Ava, last Chen, middle name cell blank:

• =CONCAT(A2," ",B2," ",C2) → Ava Chen (extra space where the middle name is blank).
• =TEXTJOIN(" ",TRUE,A2,B2,C2) → Ava Chen (blank middle name skipped).

That ignore-empty behavior is the practical reason TEXTJOIN appears in Domain 4 tasks involving incomplete name or address parts.

Putting it together on a project sheet

A typical MO-210 scenario might give columns for first name, last name, and department code, then ask you to:

1. Create a lowercase email local-part with =LOWER(CONCAT(A2,".",B2)).
2. Build a badge label with =TEXTJOIN("-",TRUE,UPPER(C2),A2,B2).
3. Report character counts with =LEN(D2) on the finished label.

Work formula-first: enter the correct function in row 2, confirm the sample result, then fill down. Keep delimiters as quoted text (" ", "-", ", "). Spread answer indices across quizzes in practice so you do not memorize letter positions—focus on what each argument does.

Master UPPER, LOWER, LEN, CONCAT, and TEXTJOIN—and know when TEXTJOIN’s delimiter and ignore_empty beat a manual CONCAT—and you complete the Format and modify text skill area for MO-210.