Exporting OL2k contacts so street number and name go into different fields?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Tong - 11 Jan 2006 13:22 GMT

Our local residents association has each subscriber listed as a separate
contact in Outlook 2k. We want to print labels for a mailing by volunteers
and it would be a big help to pre-sort the envelopes by street name first.
Problem is I can't see how to do it.

I tried exporting the contacts as a 'Windows csv' file and opened it in
Excel 2k hoping to sort on street name. But, although town and postcode
appear correctly in the fields 'Business City' and 'Business Postcode', all
of the rest, say '1 Acacia Gardens, Suburbia', goes into 'Business Street'
with the two other fields 'Business Street 2' and 'Business Street 3' empty.

Can anyone advise how we can get '1' to go into 'Business Street', 'Acacia
Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street 3'?

Signature

David Tong
[Please replace 'invalid' with 'com' to reply].

Reply to this Message

Diane Poremsky [MVP] - 11 Jan 2006 13:41 GMT

text to columns in excel?

Signature

Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide)
Need Help with Common Tasks? http://www.outlook-tips.net/beginner/

Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com
Subscribe to Exchange Messaging Outlook newsletter:
EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM

> Our local residents association has each subscriber listed as a separate
> contact in Outlook 2k. We want to print labels for a mailing by
[quoted text clipped - 12 lines]
> Can anyone advise how we can get '1' to go into 'Business Street', 'Acacia
> Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street 3'?

Reply to this Message

David Tong - 11 Jan 2006 16:47 GMT

Diane,

Thanks for the great tip. The answer is now in sight :-]

The bad news is I may have to modify all the records in Contacts so that I
have a suitable delimiter. During previous experiments I'd entered most of
the addresses into the Outlook records by pressed Enter after the house
number. If I'd used a space or a comma I'd have been alright because Step 2
of the 'convert text to columns wizard' offers these as recognisable
delimiters - but carriage return seems not to be an option.

Do you know if it's possible to indicate CR as a delimiter? There's a box
marked 'other' but it seems to accept only a single conventional character.

Signature

David Tong

[Please replace 'invalid' with 'com' to reply].

> text to columns in excel?
>
[quoted text clipped - 18 lines]
>> Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street
>> 3'?

Reply to this Message

Diane Poremsky [MVP] - 11 Jan 2006 17:27 GMT

not that I know of... I'd try exporting to CSV format (or save the workbook
as CSV), open it in word, do a search and replace on the Cr and save - open
in excel and text to column it. It's possible that excel can do a search and
replace and you can save a step, but I know it's quite easy in word.

Signature

> Diane,
>
[quoted text clipped - 33 lines]
>>> Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street
>>> 3'?

Reply to this Message

David Tong - 11 Jan 2006 20:50 GMT

Diane,

Thanks for all your help. You've solved the basic problem for me. I've
decided to correct all my Contacts entries so they have a consistent format,
and to include a comma after the street number for use by the 'convert text
to columns wizard' after I've exported the Contacts to an Excel file.

There still seems to be something strange though. After lots of test
exports I find that the field 'Business Street' always contains the street
number and the first line of the address, and 'Business City' is always
correct. But 'Business Street 2' and 'Business Street 3' are always empty
in the exported file, even when there are extra lines in the address between
the first one and the City.

Am I missing a trick here, or is this normal behaviour?

Signature

David Tong
[Please replace 'invalid' with 'com' to reply].

> not that I know of... I'd try exporting to CSV format (or save the
> workbook as CSV), open it in word, do a search and replace on the Cr and
[quoted text clipped - 41 lines]
>>>> Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street
>>>> 3'?

Reply to this Message

Phillybanker - 14 Feb 2006 14:52 GMT

Hoping you see this post/query -

I am stuck at searching and replacing on the CR in word. In the Find box,
what do you type to search for a CR? I already tried searching for a manual
line break eg. ^l and a manual page break eg. ^m but there were none found.

My sitation is similar to D. Tong's - I have exported contacts from Outlook
into excel for a merge word document. The address field has a hard return
and I cannot use the 'convert text to columns' function.

Thoughts?

Also, is there a way to select the fields you want to export to save time?
I have 4,500 contacts and its takes approx. 2 hours to export to excel.

Thanks - Howard

> not that I know of... I'd try exporting to CSV format (or save the workbook
> as CSV), open it in word, do a search and replace on the Cr and save - open
[quoted text clipped - 38 lines]
> >>> Gardens' into 'Business Street 2' and 'Suburbia' into 'Business Street
> >>> 3'?

Reply to this Message