AI for data cleaning!

I use AI for data cleaning – there I’ve told you my secret! I’m not going to jump on the devisive AI bandwagon and write about the imminent rise of the machines, or how people are getting lazy. I thought I would entertain you with how I use AI to make my life easier and be a little bit better…

To give you some background, the focus of my work is around data management, mostly for micro, small and medium charities. I like nothing more than cleaning up crap data. Names, addresses, gift histories, coding, tables, and data structures. This means I do a lot of repetitive tasks, that are essential, but quite boring and long-winded. 

Over the years I’ve honed my skills so I have a suite of functions stored in my head that I use to clean up the usual suspects, but it still takes time. 

Well not any more!  

I decided that the way forward was to set up a ribbon in Excel and add in loads of buttons that can do things quickly. AI has helped me by writing some complicated VBA (a sexy bit of code), based on my very strict instructions, tried and tested over years of doing this manually. 

Here are my top 5 – AI for data cleaning – tips!

Format my data for ease of use

I’m blind as a bat and as I’ve got older I’ve found I need to have the data in an spreadsheet formatted in a specific way so I can read it clearly on the screen. I select everything using CTRL+A and with a single click all my data is now nicely spaced and legible, with text separated from cell borders.

Donorfy heading

I often do a lot of update imports into Donorfy. I made a cheeky little button that renames the sheet Constituent Update, so I don’t have to type it in every single time. Saves time, and stops typos, win win.

Expand short address items 

This one makes Rd Road and Ave Avenue. Possibly not essential, but when I’m being paid to clean up a messy data file, I’ll do it properly. Select the data with the rogue elements in, press the button and they’re gone.

Format phone numbers 

I’ve developed four buttons that format phone numbers based on their type: mobiles (5 6), and other numbers that have 3 4 4, 4 3 4 or 5 6 configurations. I’ve also added a flag to tell me what’s been changed or identifies content that isn’t a phone number.

Mc and Mac 

Select the names you want to format correctly, press the button and this will make sure the M is upper, the c is lower and the next letter is upper. The same for Mac. 

I’ve got some others in there as well.  

Trim and Clean, Proper Case, Clean and Flag Postcodes. Over time I’ll also add some more. 

If you have repetitive tasks, there’s value in taking time to build your own ribbon. And don’t worry if you don’t know how to do it. Ask your AI of choice to help you. Start by telling it what you want to achieve, give it the instructions for the first couple of buttons and then ask it to walk you through how to build your ribbon.* 

AI should be about helping you to work smarter, and this seems a pretty smart place to start. 

* But to be honest, that is what I’m here for contact us and we can drink tea whilst we do it!