Text Cleaning Formulas for Messy Data
Fix spaces, case, characters, and formats.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Clean messy text data with formulas. App: [SPREADSHEET_APP]. Describe the messy data and show examples (fake data): [MESSY_EXAMPLES]. What the clean version should look like: [DESIRED_FORMAT]. For each problem, give a formula in a table: Problem | Example in | Formula | Example out | Notes. Cover: extra spaces (TRIM); non-printing characters and line breaks (CLEAN, SUBSTITUTE with CHAR(10)); non-breaking spaces (SUBSTITUTE with CHAR(160)); inconsistent case (PROPER, UPPER, LOWER, and the limits of PROPER for names such as McDonald or O'Neil); removing or replacing characters (SUBSTITUTE); extracting parts (LEFT, RIGHT, MID with FIND); numbers stored as text (VALUE, or multiply by 1); phone number and postcode formatting; consistent date text conversion (DATEVALUE and the risk of day/month confusion); removing duplicates after cleaning. Then combine the formulas into one cleaning formula for my case, with LET if my app supports it, explain the order of operations, and show how to convert formulas to values safely (copy, paste as values) while keeping the original column. Include a check method (compare LEN before and after, count blanks) and a warning not to overwrite raw data.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.