Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I merge data in several columns in Excel or Access?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I merge data in several columns in Excel or Access?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I merge data in several columns in Excel or Access?
"Stephane Quenson" wrote: 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I merge data in several columns in Excel or Access?
"Stephane Quenson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge excel from access data | Excel Discussion (Misc queries) | |||
How do I merge 2 columns of numerical data? | Excel Discussion (Misc queries) | |||
VLookup to merge Access Query data into Excel Report | Excel Discussion (Misc queries) | |||
There should be a mail merge feature between excel and access. | Excel Discussion (Misc queries) | |||
How to merge rows of data in 2 columns into 1 col. | New Users to Excel |