PC Review


Reply
Thread Tools Rate Thread

Re: Address fields in columns...

 
 
zvkmpw
Guest
Posts: n/a
 
      17th Aug 2012
> 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fields within fields that cross-ref to other fields and do not upd Marc Trumpi Microsoft Word Document Management 1 9th Jul 2008 03:27 PM
How to convert text fields to numeric fields without losing data in text fields. Frank Microsoft Access Form Coding 24 19th Jan 2008 12:22 PM
How are LDAP fields mapped to Address Book fields? =?Utf-8?B?bWFyZ29s?= Microsoft Outlook Contacts 2 18th Dec 2005 11:52 AM
Splitting address fields into 3 separate fields Shirley Microsoft Access VBA Modules 1 10th Jan 2004 02:37 AM
copy billing address to delivery address fields khinester Microsoft Access Forms 4 4th Aug 2003 10:01 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:47 PM.