Cleaning Text: TRIM, Case, CONCAT and SUBSTITUTE

The loyalty sign-up form lets customers type their own names, and it shows. The sheet has " mEERA iyer ", "ASHA RAO" and "dev menon", and phone numbers written as 98450-12345. None of these will match the orders sheet in a lookup. Spreadsheet text functions fix them in bulk, without retyping a single name. On this sign-up sheet, names are in column C and phone numbers in column D; Meera is on row 4.

The rule

Trim the edges, fix the case, swap what's wrong, then join.

1. TRIM removes spaces at the start and end, and squeezes every run of spaces inside down to one. =TRIM(" mEERA iyer ") → "mEERA iyer"

2. Case functions make capitals consistent:

• =UPPER("dev menon") → "DEV MENON"
• =LOWER("ASHA RAO") → "asha rao"
• =PROPER("mEERA iyer") → "Meera Iyer", capital first letter of each word, the rest lower-case.

3. Swapping characters comes in two flavours:

• SUBSTITUTE(text, old, new) swaps by content: every copy of old becomes new. =SUBSTITUTE("98450-12345", "-", "") → "9845012345". It is case-sensitive.
• REPLACE(text, start, count, new) swaps by position: starting at character start, remove count characters and insert new. =REPLACE("C01", 1, 1, "K") → "K01".

4. CONCAT joins pieces end to end with nothing between them, so add any separator yourself. The & operator does the same job. On the orders sheet: =CONCAT(D2, "-", A2) → "C01-1001", the same as =D2 & "-" & A2.

Functions nest, and the innermost one runs first: =PROPER(TRIM(C4)) trims, then fixes case.

The shortcut

Ask one question to pick the swapping function: "Do I know what to change, or where it is?" What → SUBSTITUTE. Where → REPLACE. Then build the formula from the inside out, in the rule's order.

The trap: text that looks identical but is not. "Asha Rao " with a trailing space fails an exact lookup against "Asha Rao". When a lookup returns #N/A for a value you can see, TRIM first.

Drills

Answer fast — trust the rule.

Remember

• Trim the edges, fix the case, swap what's wrong, then join.
• Shortcut: know what to change → SUBSTITUTE; know where → REPLACE.
• Trap: invisible trailing spaces break exact matches. TRIM before you look up.