# Re: Address fields in columns...

Discussion in 'Microsoft Excel Misc' started by zvkmpw, Aug 17, 2012.

1. ### zvkmpwGuest

> 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.

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.

zvkmpw, Aug 17, 2012

### Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.