If the columns and rows are messed up when copying items to Excel from an Outlook view...
As the subject (“If the columns and rows are messed up when copying items to Excel from an Outlook view...“) says, if you've experienced this and want a fix, then read on! If you don't know what I mean, go to your Contacts folder and select the Phone List view. Now add the Business Address Street column to the view (bring up the Field Chooser dialog first), select all the items in the grid, copy, and paste into a new Excel spreadsheet. If you didn't know you can do that, then that's my bonus tip of the day. :-)
So, if you have any Contacts that contain multiple lines of information (usually 2) in a street field, Excel interprets the carriage return character as a column break; this cause things to get shifted around rather terribly and you may wind up with extra rows containing only one column of data.
So here's the trick...
Using handy ol' VB functions, we can create formula fields that will split these lines into two separate columns. Display the Field Chooser dialog, and click the New button. In the Name box, enter “Business Address Street Line 1”, choose Formula for the Type, and paste this into the Formula box:
IIf(Left([Business Address Street],InStr([Business Address Street],Chr(10)))="",[Business Address Street],Left([Business Address Street],InStr([Business Address Street],Chr(10))-1))
Do the same for a second field called “Business Address Street Line 2”, and use this Formula instead:
IIf(InStr([Business Address Street],Chr(10))>0,Mid([Business Address Street],InStr([Business Address Street],Chr(10))+1,Len([Business Address Street])-InStr([Business Address Street],Chr(10))),"")
If the Business Address Street field exists in the view, remove it and add the two new fields (they will be in the “User-defined fields in folder“ group). Now select all the Contacts in this view and paste them into Excel. Voila! No more broken rows.
You can do the same thing for Home Address Street or any other field that may have multiple lines of text. If you're feeling adventurous, try modifying the formulas to handle the Home/Business Address fields. These columns can have three or more lines, so it will take some extra effort to get this to work properly, but it is definitely possible.