Debbie Mayo-Smith: Top five Excel tricks for your database
9:30 AM Monday Sep 10, 2012
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.
But you can deal with it by judicious use of spaces - being careful with what version you've got; the older versions wouldn't do it from memory.
In other words, replace Av(space) with Avenue etc.
I.e. The letter might be addressed to Mr. & Mrs. John Doe, but you don't want the salutation line to read Dear Mr. And Mrs. John Doe, you'd probably want Dear Mr. And Mrs. Doe., or Dear John and Joanie.
I would also recommend the use of filters. They're a great way to help you standardize data by seeing what's actually wrong with it. You can do a custom filter for Ave. To see how many of them exist so you don't end up converting Avenue to Avenuenue as suggested by the other commenter.
Back to concatenation. I personally never use the concatenate function. I prefer to have more control over it (since I might want to include formulas within the concatenation. I prefer doing something like:
=a2&" "b2&", "&c2
That will take what's in A2 add a space after, add B2, add a comma directly to the right of B2 then a space and finally c2. I feel this is far more user friendly and more functional.
You can get around some of this by replacing with a dummy string first. So you replace Avenue with Axxx, then Ave with Axxx, then Av with Axxx, and finally replace Axxx with Avenue.