Make data into columns

[SteveB2004]

I have imported Customer names & address that I wish to use as a dbase. Each name / address is imported into 1 column, ie
mr smith
123 anystreet
London
ec1 2ab

Next Recorded
etc
etc

There are no commas. How can I change these records to be in columns as follows
name, address1, address2. post code

I know I could use cut & paste but there are a lot of records.

[MartinShort]

You can use the "transpose" command in the Paste Menu/PasteSpecial Menu. It still involves a little work on your part, but reduces the cutting and pasting required.

I am assuming that all the records are in column A. So

1. Select the 1st record & click copy.
2. Select B1
3. Select the dropdown arrow by Paste->Transpose

If you're feeling really brave, make your selection (FIRST!) and then try recording a macro to do the donkey work for you. You can attach the macro to a button/shortcut command etc.

[SteveB2004]

Thanks for prompt reply, I will try it out.
Thanks Again
Steve

[Julia Osborne]

Its amazing isn't it - this is such a common thing to have to do, especially when you've grabbed addresses from other sources, and still there is not a standard M/Soft importing tool that can cope with this scenario. I bet we all end up copying/cutting and paste transposing - a macro option is OK, but it very quickly goes pear shaped if the data blocks are different lengths/numbers of rows, especially if your VB is not good enough to modify the code to take account of this. I've tried it all sorts of ways, but I've never yet beaten paste transpose for a solution.

A useful tip for importing all sorts of data is to whack it into notepad and save as text, and then open up the text file in excel and use the import wizard to designate the column headings, but it won't work in this scenario - if anyone ever solved this one comprehensively I'd be interested.

Julia

[MartinShort]

Ah. I can see why you say that Julia. The thing is that this sort of stuff is really meant to be handled by Access rather than Excel (mind you we all use Excel for this kind of thing).

Also the flat file record length is usually different (ie some have 3 lines, some have 4, etc). Now a software house that insisted on using flat files - for example the company which collects data for the European Motor Insurance Database - would put record headers and footers such as *Record Start* and *Record End* and even *no of Records = x* in the file if memory serves me correctly (Although from a programming point of view, it should be fairly easy to deduce the number of records.

eg

*No of Records = 2*

*Record Start*
mr smith
123 anystreet
London
ec1 2ab
*Record End*

*Record Start*
mr Jones
456 anystreet
London
ec1 2ab
*Record End*

Now even with the above, you would need to add some programming to loop through each record and transpose it to a spreadsheet format.

Moral of the story: Go back to the author and tell them to get their act in gear and either produce the records in Access or do it properly in Excel from the start.

[Julia Osborne]

Hi Martin

I'm a real Access Fan myself - I've never found anything I can't solve with a combination of it and the rest of Office, and have done all sorts with it.

Yes, I can see the 'record start' and 'record end' concept. I've been reading a bit about text export formats, the sort of way that Word forms can export out to excel, I guess once you have some common terms in the text string you can do things with it. However, I wonder if it is possible to write a macro/VB to put the start and end strings into the data in the first instance - its a bit beyond me to suss the syntax, but someone else might - how about this for a concept:

Every address would have to be checked for a starting salutation (Mr, Mrs, Sir, Dr etc) - dummies would have to be added if not, but it would be present in many situations.

Could you then loop through the data a row at a time testing for the presence of the salutation character strings probably only 5 or 6 maximum - Mr, Mrs etc (which is unlikely to be present in the rest of the address and usually has a standard format), and when it is encountered program a 'insert 2 new rows above and insert 'Data end' in the first and 'Data start' in the second'. This should effectively split the column up with the Data start and end criteria in the correct places. You then just manually remove the Data end at the top of the column, or if you are really flash get the code to lose the top one.

The same code could finally loop through the data again removing blank rows from the column (I know this is possible as I have some code for this) which would then be ready for further work.

Well, what do you think - Is there anyone out there that likes the odd programming challenge - I don't think it sounds too difficult in principle though I don't know the grammar of code to do it myself.

Julia

P.S. It might also be possible to check for postcodes as they often have a similar format, esp if you have a reference list of all codes to check them off against