Importing .txt file into Excel

[Simon]

Hi, I have a long list of names and address (text document .txt), the list
looks like the following

All Saints, Carnegie St
corner with Caledonian Road
Islington
N1 9QD
GB
Denomination Church of England
Phone 020 7837 0720
Fax
Email

Brixton - UCKG HelpCentre
386-388 Brixton Road
London
SW9 7AW
GB
Denomination Pentecostal
Phone 020 7738 4887
Fax 020 7738 4887
Email
Web Site

Etc etc

How do I import this list into specific columns in Excel, such as Company,
Address1, Address 2 etc?
Any help would be greatly appreciated.
Thanks
Simon

 

[Dave Peterson]

Does your data always come in the same order? Line 1 of each group is always
the same; line 8 is always the Fax number, etc???

And your two examples have a difference. The top is missing the Web site.

Was this a mistake in the post or does your data vary between each grouping?

A little more info may help you get an answer.

 

[Simon]

No, the information does not always come in the same order, however, unless
there is an easier way that you could suggest I can edit the address's
manually so that all the details appear in the same lines etc.
I have managed to import this address list into Excel but the info appears
in one long column. I would like the info to appear under the the correct
culomn headings. Any help will be greatly appreciated.
Thanks
Simon

 

[Dave Peterson]

The bad news is I think you have to do some work first.

I see two possible solutions (probably lots of others, though).

First, make the layout of each group exactly the same.
row 1 is the name,
2 is the first address
3 is 2nd address
...
7 is phone
8 is fax
9 is email
10 is web site
11 is either blank or the start of a new grouping)

The second would be to insert some kind of indicator in a helper column (say a
new column A).

Each row of each group would have an indicator.

A B
Name
Addr1
Addr2
fax


you could skip rows of info, but as long as it had the corresponding key in
column A, you'd be ok.

(and you could tell a new group by the "Name" key in column A (each group does
have a Name, right?)

If you choose the second option, I think you'd need a macro to go through the
range. But if you went with the first option, you could use some formulas.

I'm assuming that 1-10, 11-20, 21-30, etc are groups and the pattern continues.

With your data in A1:A9999, you can put this formula in
B1:K1 and drag down until you run out of data:

=INDEX($A:$A,(ROW()-1)*10+COLUMN()-1)

Then check the results.

If it looks ok, copy|paste special|values
and edit|replace
Replace 0 (check entire cell)
with (leave blank)

This'll get rid of those 0's that came over from the empty cells.

===
The decision to choose which becomes which is easier for you.

Remember if you insert a new row (to get things back in the right sequence), you
can hit F4 to do it again.

So you'll just insert once and use the down arrow to scroll down your list.
When you need a new row to shift stuff down, you'll just hit that F4 and keep
moving. (It might not take too long.)