|
|
OUTLOOK Q&A
Reformatting contacts before moving from Excel to Outlook
By David Gewirtz
It's often a good idea to consolidate all your contacts in one place and as Outlook users, the place we most often like to keep our contacts is in the Contacts module of Outlook. Sometimes, though, getting contacts into Outlook is a bit of a challenge, especially if the originating data isn't in the perfect form.
Reader Clancy Stumpf of New Orleans was stumped about how to solve this problem:
I just read the instructions you wrote in Outlook Power Magazine, Moving contacts from Excel Into Outlook. The instructions were very helpful but I have a question.
The contact data that I am trying to map over from excel to Outlook is broken down very specifically into cells: First Name, Last Name, City, State, Zip, etc.
When I was mapping it over, the Outlook format was not the same. For example, the Outlook address field format is the entire address but on my spreadsheet, the address is broken into separate cells: Street address, city, state, zip. What is the solution here?
That's what's sweet about Excel. Formulas. Use formula to construct the data you need. Probably a good idea to make a copy of your Excel file and then hack the copied file, creating new combined fields (don't forget the space between names, if you do it that way), and then export the calculated fields.
I actually like to massage data in FileMaker, but it's a pretty costly purchase if that's all you're going to do -- and you can do it all quite easily with Excel. You're looking for the string concatenate functions, which in Excel are called CONCATENATE.
Let's say column A is all first names and column B is all last names. If you wanted to export data that's the combination of first names and last names, you'd create a column C that contained a formula concatenating the two together.
For example, if A1 contained "David" and B1 contained "Gewirtz", typing the formula =CONCATENATE(A1,B1) would result in...
...did you notice the problem?
That's right. CONCATENATE(A1,B1) would result in "DavidGewirtz". But that's not really what we want, is it? We want "David Gewirtz", with a space between David and Gewirtz. So, how would you go about fixing that? It's actually quite simple. You'd use:
Of course, that only concatenates the first cells in columns A and B. To concatenate elements for the entire row, you'd use:
|
|
|
|
