Debbie Mayo-Smith: Top five Excel tricks for your database

Photo / Thinkstock

I was Microsoft's guest this past week at the Tech Ed Conference which heralded the release of Windows 8 and Office 365. The conference was geared for the technical field. The guys and gals who run IT departments and will build the new apps for the Windows phones and tablets. Not mere mortals and users like me.

Not much was said about The Office features, as attention was focused on the new Windows 8 and Office 365, the latter being their Cloud based software.

While the demos were on prototype tablets, it will be marvellous to have the ability to work with your Office documents anywhere. The editing is as rich as if you were working straight in Word, PowerPoint or Excel. I can't wait to be able to kiss Google doc's goodbye so I can do REAL work while overseas.

See my previous column To tablet or not overseas.

One of the new Excel features demonstrated seems to have overcome a problem of not knowing what functions can help you manipulate names, emails and addresses. If it works well it will be a huge innovation helping immensely.

In the interim, I thought I might give you my top 5 Excel functions for working with data. This can save a massive amount of time. By the way, to move information from one software program to another, simply save the information as a CSV (comma separated variable) or a TAB (text) file. Then open it in Excel.

Problem One: First and last name together in one column.

You want to personalise and not send Dear John Smith.
Solution: (Data menu> Text to Columns)

This function has a 3 step wizard that separates out information from within one column. It asks you what to look for (blank space, a comma) and then asks if you want any special formatting. That's it! Just be sure to add a lot of extra blank columns for the information to go to - some people have last names like van de Whitten!

Problem Two: Inconsistencies

You want to do a mail merge, but you have Ave, Av, Avenue.........
Solution: Find and Replace (Edit menu > Replace)

Enter Ave for find, and type in Avenue for replace and then select replace all. Do it again with Av to Avenue and so on.

Take a peek behind the fx button - looking at four functions that are particularly wonderful when working with text. I'll also detail how functions work.

Problem Three: Mixed upper and lower case.

This could be from the way you store your names or from accumulating data from website entry. You don't want to send out letters or emails that say Dear JOHN or Dear ROSE do you?
Solution: (fx) Proper

A veritable gem. Proper will change the first letter of each word to upper case, the rest to lower case. So debbie mayo-smith becomes Debbie Mayo-Smith

Additionally - there are the functions Upper which will turn the information into all Upper case and Lower which turns it to all lower case.

Problem Four: Information in many columns that need to be put together in one

For example you have a list where the first and last names are separate, but your database requires them to be together.
Solution: (fx) Concatenate

This will merge separate Columns together. So if you have Mr & Mrs, Tom, Jones in three different columns, Concatenate will put them together into one column.

Problem Five: extra blank spaces got into your database

From incorrect data entry or some other way
Solution: (fx) Trim

Removes all extra spaces in a column except for ones that should be there in-between words.

Written by international speaker and bestselling author Debbie Mayo-Smith. For more tips, over 500 how-to articles visit Debbie's article webpage.