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

[David Tong]

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'?

 

[Diane Poremsky [MVP]]

text to columns in excel?

 

[David Tong]

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.

--
David Tong

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

text to columns in excel?

--
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/

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'?

 

[Diane Poremsky [MVP]]

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.

--
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/

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.

--
David Tong

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

text to columns in excel?

--
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/

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'?

 

[David Tong]

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?

--
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
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.

--
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/

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.

--
David Tong

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

text to columns in excel?

--
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/






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'?

 

[Guest]

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
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.

--
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/

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.

--
David Tong

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

text to columns in excel?

--
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/






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'?