Word contacts list to Excel

[shawkz]

Hi, i have a word document which is in the following format...

name
address1
address2
address3
address4
postcode

name
address1
address2
address3
address4
postcode

etc...

I want to be able to import it into excel into the following format...

name | address1 | address2 | address3 | address 4 | postcode.


Please can anyone help?

Kindest thanks,

SH

 

[shawkz]

I can copy the entire contents from word into a single column in excel, can this be reformated so each line is a new column if an empty line is found then it starts again?
Not sure if there is a macro which will do this?

Hope this helps!

regards.

 

[buitenhek]

I sell Excel based bidding packages so I use only rote functions in my spreadsheet designs (no viruses that way) so my solution will be tedious. However, I am certain macro guru can easily help you.
My 2 possible ways
Solution 1
1a. Copy list from Word and Paste Special (text) in Excel
1b. Highlight 1st group, copy and Paste Special (Transform)
1c. repeat ad infinitum
problem-if you change address in Excel page 1, the horizontal list will not update
Solution 2
2a.Copy same from Word and Paste Special (text) in Excel to Page 1
2b.Go to page 2 highlight 6 columns and use Transpose function
2c.Pick 6 rows of person 1 for the data on Sheet 1
2d.press F2 then hold Cnrl-Shift-enter to make array formula
2e.Page 2 will now have person 1 data
2f. highlight page 2 first row (6 columns of person 1) and grab handle to copy contents down 6 rows
2g.delete rows until you see person 2 info
2h. repeat ad infinitum
this will update on page 2 the info you change on page 1 of spreadsheet
Sorry for the mumbo jumbo

Regards
Peter Buitenhek
ProfitDeveloper.com

 

[dcompto]

See thread707-1156096.

 

[rodesh]

If all addresses are of same no. of fields, you can make data as you want using indirect function and series fill.

1. First copy data to the column A
2. In cell B1 enter cell reference of first name data that is A1 and in cell B2 enter cell reference of second name data that is A8
3. Enter the formula "=INDIRECT(B1)" in cell C1 and "=INDIRECT(B2)" in cell C2
4. Select range "B1,B2,C1,C2" and drag clicking right corner dot to fill series until you get last data of name

5. use same techniques for other fields as well


A B C
1 name1 A1 =INDIRECT(B1)
2 address11 A8 =INDIRECT(B2)
3 address21
4 address31
5 address41
6 postcode1
7
8 name2
9 address12
10 address22
11 address32
12 address42
13 postcode2
14
15 name3
16 address13
17 address23
18 address33
19 address43
20 postcode3
21
22 name44
23 address14
24 address24
25 address34
26 address44
27 postcode4



The result will be
B C
1 a2 name1
2 a9 name2
3 a16 name3
4 a23 name44