My husband Steve walks in and peers over my shoulder for a moment. He asked "what on earth are you doing Debbie?" "Oh, honey," I moaned. I want to send out my first email newsletter tomorrow and do a personalised email merge with my database. But everyone's first and last names are in one column, so I'm splitting them apart."
(Have you ever done that copy paste delete, delete dance? Copying the names into two columns, then deleting the last name from the first column and then the first name from the last column? Over and over and over and over again?)
Steve's seven words were: "Don't do that! Use text to columns" "Text to columns? What's that? I never heard of it" I replied. Steve then had me hand him my laptop and he proceeded to show me a function in Excel that with 3 clicks of the mouse split the 2890 names (I had left to do) apart. Instantly. Three clicks instead of at least two, three hours.
You're thinking two things now. Cool. But life changing? It was to me because:
First, emphasized how little I, we know our software. The hidden diamonds beneath the ribbons. The swipes on our phones we don't know to do. Wonderful things waiting for us to discover and save us time.
Second it started my quest for the modern Holy Grail. Time. And saving it. From that moment on I promised myself that when anything repetitious in nature had to be done on the computer, I would first stop. Think. Question my new best friends Mr G (Google of course) and Microsoft 'Help'. I knew now that for most things we do around software there is a quicker, smarter, better way.
There. Hope you like that story. Now on with the wow bit for you.
One of the most useful ways of using Excel is as a list of names and addresses. Your database. However as this list grows over time lots of problems can arise:
1. You add on details collected from the internet (online signups for your newsletter or requests for quotes for example).
2. Different people interact with it and have different habits - such as using all upper case in places or putting abbreviations, not adding on prefixes for phone numbers, mixing landline and mobile numbers in one column.
3. Duplicates can occur
4. Mistakes such as comma's in email addresses instead of periods in places
If you need to use this list for emailing or mailing - you have to clean it - or look bad! Just as I didn't want to send an email newsletter out Dear Tom Cruise, Dear Samantha Smith -or Dear reader, it has to be fixed first.
Here are a few lovely, lovely tricks.
Wow! I didn't know that.
1. OneDrive (free online MS Office) has a splendid version of Excel online that you can share- thus allowing people anywhere, anytime to enter their own contact details. All they need is the link. Sign up here.
2. Online Survey with OneDrive also has a stunning online survey/form capacity. You can create a form, a survey, a RSVP reply and send the link. What you get is a secure database of the responses all set and done for you. Easily downloadable.
3. Text to columns - in the data menu 2003; data ribbon 2007-13 will split apart information into separate columns based on what you tell it: space, comma, dash.
4. Concatenate - will then put things from multiple columns back into one column. For example your last names like van der Geen.
5. Proper - turns text into proper tense - capital for the first letter.
6. Text Filter - you can search within columns for specific text using the 'contains, does not contain' parameter.
7. Export to Outlook - bet you didn't know you can not only import your Excel database into Outlook but you can also do the opposite - Export your Outlook contacts into Excel.
Written by Debbie Mayo-Smith, One of New Zealand's most in-demand speakers, trainers and bestselling authors. Debbie works with companies that want more effective staff. For more tips and business ideas sign up for her free monthly newsletter. 
