Course / Working with Text

Cleaning up messy text

Fix stray spaces, random capitals and inconsistent codes in imported data. Intermediate · 5 min · TRIM, PROPER, UPPER, LOWER, SUBSTITUTE

Data exported from other systems is rarely tidy. Names arrive in CAPITALS, emails in a mix of cases, and stray spaces hide where you can't see them. Fixing it by hand is slow and easy to get wrong. Five functions do most of the work:

• TRIM removes spaces at the start and end, and squeezes runs of spaces down to one
• PROPER capitalises Each Word, UPPER makes EVERYTHING CAPITALS and LOWER makes everything lowercase
• SUBSTITUTE(text, old, new) swaps every occurrence of one piece of text for another

Clean the data with formulas in new columns, and keep the original next to it so you can always check your work.

Steps

1. The imported names in column A have extra spaces at the start, at the end and in the middle. In D2:D8, tidy them with =TRIM(A2).

2. Now fix the capitals too. Change D2:D8 so that TRIM sits inside PROPER: =PROPER(TRIM(A2))

3. Email addresses don't care about capitals, but mixed case looks messy and trips up duplicate checks. In E2:E8, convert each email to lowercase.

4. Booking references should be in capitals. In F2:F8, convert them with UPPER.

5. Remove those inner spaces by substituting every space with nothing (""). Change F2:F8 to =SUBSTITUTE(UPPER(C2), " ", "")

Remember

• TRIM removes extra spaces, and PROPER, UPPER and LOWER fix capitals.
• SUBSTITUTE(text, old, new) replaces every occurrence. Use "" as new to delete.
• Nest functions to do several clean-ups at once: =PROPER(TRIM(A2)).
• Keep the raw data and clean it in a new column.

Previous: Splitting text at a character · Next: Formatting numbers in text with TEXT