How can I merge data in several columns in Excel or Access?

[Guest]

In Access I have a large database with 7000+ records. I want to combine the
data in 6 columns in this. I could not find a way to do it in Access so
copied it to Excel. I can apparently merge cells in Excel (and using CHAR(10)
between fields which will make things very neat) but it does not tell me how
to merge complete columns in one go.
I am using Vista Home Premium and Office 2007.
Can anybody please tell me - step by step - how I can do this? Assuming it
is possible.
Many thanks.

 

[Guest]

If you have imported the records into excel, you have below the header row on
row two several fields, so you should add a column on the right with the
formula on row two:
=A2 & Chr(10) & B2 & Chr(10) & C2 & Chr(10) & D2 & Chr(10) & E2 & Chr(10) & F2
Then you copy this formula and paste it on each row (select cell G3, then go
down with the right "lift", then press Shift and click on cell G7001, then
press Enter to copy the formula.

Now if you want to save this as a text file, select column G and paste it to
notepad or your favorite text editor.

If I have wrongly understood your needs, let me/us know!

Stephane.

 

[Guest]

If you have imported the records into excel, you have below the header row on
row two several fields, so you should add a column on the right with the
formula on row two:
=A2 & Chr(10) & B2 & Chr(10) & C2 & Chr(10) & D2 & Chr(10) & E2 & Chr(10) & F2
Then you copy this formula and paste it on each row (select cell G3, then go
down with the right "lift", then press Shift and click on cell G7001, then
press Enter to copy the formula.

Now if you want to save this as a text file, select column G and paste it to
notepad or your favorite text editor.

If I have wrongly understood your needs, let me/us know!

Stephane.

Hi Stephane

Thank you for taking this on board.

Firstly I am a complete and total novice at Excel so please make your
instructions very basic and step-by-step. Access I am not too bad at.

I tried your suggested formula and I couldn't get it to work. That may not
be the formula - it may be me doing something wrong.

In the original Access database the 6 fields that I wish to merge are all
'memo' fields at present. I want the single field that results from the merge
to be a 'text' field.

Thanks in advance.

Brian (Swiftsilver)

 

[Guest]

If you have imported the records into excel, you have below the header row on
row two several fields, so you should add a column on the right with the
formula on row two:
=A2 & Chr(10) & B2 & Chr(10) & C2 & Chr(10) & D2 & Chr(10) & E2 & Chr(10) & F2
Then you copy this formula and paste it on each row (select cell G3, then go
down with the right "lift", then press Shift and click on cell G7001, then
press Enter to copy the formula.

Now if you want to save this as a text file, select column G and paste it to
notepad or your favorite text editor.

If I have wrongly understood your needs, let me/us know!

Stephane.

Sorry, Stephane, but it is not working! Maybe I need to explain more fully.

In Access I have a database for recording details and sightings of railway
locomotives. There are a lot of different fields for different purposes, but
6 fields are the actual sightings fields, labelled: Sighting, Sighting1,
Sighting2 and so on.

When a locomotive is seen an entry is made in the next free field. Entries
are all in the same format, as: '13/08/07 - Doncaster Station.'

I am running out of fields as I have been recording locomotive data for so
long and I do not have space for more fields, so I want to merge all the
'sighting' fields into one which, in Access, will have to be a text field. I
want the merged format to look like:

22/02/05 - Birmingham Station
11/11/05 - Thornaby Marshalling Yard
01/03/06 - Newcastle yard
23/09/06 - Peterborough TMD
13/08/07 - Manchester Piccadilly Station.

and I must be able to add further entries in future.

While I have some knowledge of Access I am a rank beginner at Excel and need
step b y step instructions!

Many thanks

Swiftsilver