Lesson 2.2 — Clean a messy client export

Transcript preview

Real client data is never clean. People type city names six different ways, phone numbers in four formats, and dates however they feel that morning. Cleaning that up is unglamorous, and it pays every single month. Look at this. Four rows, and every one is broken differently. Trailing spaces, all caps, underscores, and four different spellings of the same city. A normal formula cannot fix this because there is no pattern to match. And here is why this is different. One prompt handled all four. It knew B L R is Bengaluru. It knew bangalore comma k a is the same city. You did not write a single regular expression, and you never will again. These four prompts will handle about ninety percent of the cleaning work you ever get paid for. Proper case names. Standardise to a ten digit Indian mobile number. Convert this date to year, month, day. And return only the company name without the legal suffix. Price this at eight to fifteen thousand per dataset. And the beautiful part is that it repeats. Clients come back every quarter with a new export and the same problem.…