Address fields in columns...
 

Hi - one day i'll be able to help but alas my excel quest leads me to learn rather than coach until that time comes, i need your help.

i have 10,000 names and address in a .txt form which i need to import into excel, save as a .csv. The names are formatted as below:

Name : fred blogs
address: 1 the street
addres 2:
address 3: Derby

plus the usual fac line etc...

(10000 times in column a1:b1)

how am i able to have the data under one heading and 10000 rows....?

your help would make me very happy!!


nick

Hi Nick,

Can we have some before and after dummy data in Excel format?
Makes it far easier to help.

S.

Hi Spencer

For su

Address details from the *.txt file are formated as follows

Name: Fred Blogs
Address1: The Street
Town: New Town
County: Sussex
PC: AB1 2CD
Telephone: 01234 567890
Fax: 01234 567899

Name: Jane Blogs
Address1: The Old Street
Town: Old Town
County: Sussex
PC: DC2 1BA
Telephone: 0987 654321
Fax: 0987 654322

10,000 more names...

All names are formated in this way. When I import the text using the wizard it sorts the heading from the text no probs. (I add the : into the criteria and it inserts the column.

WHat I would like to achieve is he follow
Name Address1 Town County Post Code Tele FAX
Fred Blogs The Street New Town Sussex AB1 2CD
Jan Blogs The Old Street Old Town Sussex DC2 1BA

hope this is enough to help

Hi, it helps sort of. The reason I asked for dummy data in Excel format was so I could see the exact layout. Just would have sped things along.

Is there a blank row (or rows) between ach person?
Is the layout consistent throughout? i.e. 7 rows do data and then a blank / blanks? Or could this vary through the data?

Very qood point - me being special

This is the format (exactly) as it is in the TXT file.

---

Name: Fred Blogs
Date Of Birth: 01/07/1999
Address: 1 New Street
Newtown Sussex AB1 2CD
Phone: 0123 456789

---

Name: Janet Blogs
Date of Birth 01/06/1980
Address: 1 Old Street
Newtown Sussex DC2 1BA
Phone: 0987 654321

---

I know the second address line has no heading and this is going to cause me problems!

I would have thought the postcode being in the same row as the second line of the address would cause more problems than that row having no "heading".

But, before I can provide a solution, I'll be needing some sample data in Excel format. You can add an XLS or XLSX file to a compressed file (or use WinZip if you have it) and then attach that here.

I really cannot do much at all with the text examples you've posted above.

S.

Address fields in columns...
 

I know the second address line has no heading and this is going to cause
me problems!

I think not, but anyhow there's a more ominous question.

Wikipedia shows that county names in the UK can be from one word to five words. Presumably, town names can also be one or more words. So the question is how to mechanically split a cell to separate those two names. Is there something in the raw data that would help? Otherwise, there could be some kind of matching with county names in a fixed list.

Alternatively, would it be acceptable for the result to have town and county combined in a single column?

Address fields in columns...
 

This is the format (exactly) as it is in the TXT file.


---

Name: Fred Blogs
Date Of Birth: 01/07/1999
Address: 1 New Street
Newtown Sussex AB1 2CD
Phone: 0123 456789

---

Name: Janet Blogs
Date of Birth 01/06/1980
Address: 1 Old Street
Newtown Sussex DC2 1BA

Phone: 0987 654321

---

Here's one way to approach the problem. It relies on the regularity of the source data, by counting rows and

characters within rows. It uses a fixed list of county names to parse the second address line.

Sheet1 column A holds the source data. It starts at A1 with the first "---" that begins the first data record.

Sheet3 will hold the result array.

Sheet2 is for intermediate results. Its rows parallel Sheet1 (with two extra rows on top).

Once sheets 2 an 3 are set up, they are not edited when new source data is pasted into sheet 1.

Let's start with Sheet2, and then do Sheet3.

------
Sheet2
------

In A3, put
=TRIM(Sheet1!B2)
and copy down as far as needed. This keeps only the part of the source data that contributes to the result.

In B3 put
=LEN(A3)
and copy down as far as column A can go. This is the length of each "column A" value.

In C3, put
=IF(MOD(ROW()+1,8)=0,
IF(MID(A3,B3-6,1)=" ",6,
IF(MID(A3,B3-7,1)=" ",7,8)),
"")
and copy down as far as column A can go. This is the length of each post code.

Starting in E1 an going to the right, enter the county names. If there are alternate spellings, include each

spelling separately.

Put this in E2
=LEN(E1)
and copy rightward as far as needed. It's the length of each county name.

Put this in E3:
=IF($C3="","",IF(E$1=MID($A3,$B3-$C3-E$2,E$2),E$2,""))
and copy rightward and downward as far as needed to fill the rectangular array. This compares:
- the "column A" text directly before the post code with
- the county names.
Where they match, it displays the county name's length. There might be more than one match on the same row.

In D3 put
=MAX(E3:CD3)
and copy down as far as column A can go. (If county names go beyond column CD, extend the range.) This chooses

the longest county name matched for each record and displays that county name's length.


------
Sheet3
------

Sheet 3 draws data from only Sheet2!A:D.

In A2 put
=INDEX(Sheet2!$A:$A,8*(ROW()-1)-4)

In B2 put
=INDEX(Sheet2!$A:$A,8*(ROW()-1)-2)

In E2 put
=RIGHT(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1),
INDEX(Sheet2!$C:$C,8*(ROW()-1)-1))

In C2 put
=LEFT(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1),
LEN(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1))
-1-LEN(E2)-INDEX(Sheet2!$D:$D,8*(ROW()-1)-1))

In D2 put
=MID(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1),
LEN(C2)+1,
LEN(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1))-LEN(C2)-LEN(E2))

Select A2:G2 and copy down as far as needed.

Enter the column headers in row 1.


Hope this helps getting started.