Level 2 · Fix spaces and casing in city and state

Text that looks the same is not always the same. 'Houston ' has a trailing space you can’t see in a cell. 'houston' and 'HOUSTON' look sloppy on a label and split one city into three in a pivot or a chart. This sheet holds Texas customers who signed up since September 2025, straight from the customer export, mess included.

Three cleaning functions

Function	Does	D36 = ' austin ' becomes
TRIM(text)	removes spaces at both ends, squeezes runs of spaces inside to one	'austin'
PROPER(text)	capital first letter of each word, the rest lower case	' Austin '
UPPER(text) / LOWER(text)	everything upper / lower case	' AUSTIN '

They combine from the inside out. For the city:

F2   =PROPER(TRIM(D2))

and for the state, which is a two-letter code:

G2   =UPPER(TRIM(E2))

E16 holds 'TX ' with a trailing space; G16 becomes TX. D27 holds HOUSTON; F27 becomes Houston.

Where PROPER goes wrong

PROPER capitalises after anything that isn’t a letter, which is right for San Antonio and St. Louis and wrong for some names: PROPER("mcallen") gives Mcallen, and PROPER("o'brien") gives O'Brien (right) but PROPER("it's") gives It'S. All the cities here survive it. Before you run it over a column of surnames or brand names, scan the result.

Proving what changed: EXACT, not <>

Helen wants the changed rows marked. The obvious test is =D2<>F2. It fails quietly: Excel compares text without looking at case, so "houston"<>"Houston" is FALSE. Written as =OR(D2<>F2,E2<>G2), the flag catches the four rows with stray spaces (16, 25, 36 and 43) and misses rows 8, 22, 27 and 37, where only the case changed.

EXACT(text1, text2) compares character by character, case included, and returns TRUE only for an identical match. A row is unchanged when both columns match exactly, so the flag is the opposite of that:

H2   =NOT(AND(EXACT(D2,F2),EXACT(E2,G2)))

J1 counts the TRUEs with =COUNTIF(H2:H49,TRUE): 8 of the 48 rows. Helen checks those eight labels and trusts the rest.

Keep the raw column

Never clean a column in place. Keep city as exported and put clean_city next to it. If a rule turns out wrong (a PROPER that mangled a name, say), you fix one formula and everything updates. When the sheet goes to the print shop, copy the clean columns and paste them as values into a new file.

For a variant that no function can fix, like a misspelling (Hoston), use Find & Replace (Ctrl+H): find the wrong spelling, replace it with the right one, and tick “Match entire cell contents” so Houston inside a longer name isn’t touched. Filter the column first to see every variant at once: the filter list shows each distinct spelling.

Common mistakes

• <> or = to detect changes. Both ignore case. Use EXACT.
• PROPER on the state. PROPER("tx") gives Tx. Codes need UPPER.
• TRIM alone. It fixes 'Houston ' but leaves houston and HOUSTON as they are.
• Non-breaking spaces. Text pasted from a web page can carry character 160, which TRIM leaves alone. in Excel, SUBSTITUTE(D2,CHAR(160)," ") turns it into a normal space first. Our export doesn’t have any, but you will meet them.