Text cleanup practice for messy Excel imports

Imported data rarely arrives as neatly as a tutorial example. Names may have extra spaces, emails may use mixed case, and codes may bundle several fields together. Text functions help turn these strings into consistent inputs for reports and lookups.

Clean spacing before presentation

TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words. PROPER capitalizes word starts. Combining them is a useful exercise:

=PROPER(TRIM(A2))

Apply this carefully to real names: capitalization rules vary, so automatic title casing can damage intentionally styled names. The quest’s fictional names are chosen to make the transformation unambiguous.

Normalize an email label

LOWER converts letters to lowercase. That is useful for consistent display of an imported email column:

=LOWER(C2)

This is a text transformation, not email validation. It does not prove an address exists or can receive mail.

Extract a code’s parts

For a code such as ORD-NY-204, LEFT(B2,3) returns the prefix, MID(B2,5,2) returns the city code, and RIGHT(B2,3) returns the suffix. Positions start at 1. These formulas assume the code format is consistent; variable-length identifiers need a different parsing approach.

Replace a separator

SUBSTITUTE changes matching text. To replace a hyphen with a space, use:

=SUBSTITUTE(D2,"-"," ")

The replacement space is easy to overlook. Empty quotation marks would remove the hyphen instead, joining the two words.

Combine clean fields

The & operator joins strings. Put a literal separator in quotation marks between references. Build one transformation at a time, verify it, and then nest the pieces. The Contact Restoration boss combines a clean name and email into a readable label.

Know the boundary

TRIM does not remove every kind of Unicode whitespace. Real imports may contain nonbreaking spaces or control characters. These exercises focus on ordinary spaces; inspect your source data before assuming a single cleanup formula fixes everything.

Continue your adventure

Use the formula spellbook for syntax or explore the full quest map. You can practice in the browser and download workbooks without creating an account.