Splitting field into separate excel cells.

  • Thread starter Thread starter Iona via AccessMonster.com
  • Start date Start date
I

Iona via AccessMonster.com

Hi all,

If anyone had any suggestions.. I have an access database for contact details.
. The underlying tables are split into Name, Address. When inputting the
address, the user separates the lines by hitting return:
Address1, (return) Address2, (return) Address3, (return) etc.

I also export to excel, but I need to split the Address field into separate
excel cells where the user has separated by 'return'. Can I set this up in
access?

Can anyone help?

Thanks.
 
Hi all,

If anyone had any suggestions.. I have an access database for contact details.
The underlying tables are split into Name, Address. When inputting the
address, the user separates the lines by hitting return:
Address1, (return) Address2, (return) Address3, (return) etc.

I also export to excel, but I need to split the Address field into separate
excel cells where the user has separated by 'return'. Can I set this up in
access?
Click to expand...

I'd strongly suggest having these in separate fields in Access in the
first place! As you have found, it's much easier to put things like
this together than it is to split them apart consistantly.

Try using the built-in Split() function. The problem will be that
there are surely variable numbers of lines in addresses, and
inconsistancies in how those lines are used - e.g. is the PostCode on
a line by itself or on the same line as the City?

Address1: Split([Address], 1)
Address2: Split([Address], 2)
<etc>


John W. Vinson[MVP]
 
Hi John,

Thanks for the input

Of course if I knew beforehand, id have structured the fields differently,
but naturally, the end user appears a month later asking for the different
excel!!! And without restructuring every other query and form I have, I just
need to change the excel output... a headache to be sure!

Thanks for the suggestion, could u go into more detail? Im still learinng
access

appreciated.


John said:
Hi all,
Click to expand...
[quoted text clipped - 6 lines]
excel cells where the user has separated by 'return'. Can I set this up in
access?
Click to expand...

I'd strongly suggest having these in separate fields in Access in the
first place! As you have found, it's much easier to put things like
this together than it is to split them apart consistantly.

Try using the built-in Split() function. The problem will be that
there are surely variable numbers of lines in addresses, and
inconsistancies in how those lines are used - e.g. is the PostCode on
a line by itself or on the same line as the City?

Address1: Split([Address], 1)
Address2: Split([Address], 2)
<etc>

John W. Vinson[MVP]
Click to expand...
 
Thanks for the suggestion, could u go into more detail? Im still learinng
access
Click to expand...

Create a Query based on the table. In as many vacant Field cells as
you have lines in the addresses, type

Address1: Split([Address], 1)

using your actual fieldname, for values 1, 2, 3, ... etc.

John W. Vinson[MVP]
 
Back
Top