Re: Address fields in columns...

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

  1. zvkmpw

    zvkmpw Guest

    > 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.
     
    zvkmpw, Aug 17, 2012
    #1
    1. Advertisements

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.
Similar Threads
  1. Janet

    Columns to rows/ Rows to columns

    Janet, Sep 11, 2003, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    244
    Janet
    Sep 11, 2003
  2. drquickbooks

    Reformat data from 3 columns to 9 columns worksheet

    drquickbooks, Nov 29, 2003, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    235
    drquickbooks
    Dec 1, 2003
  3. sallyember
    Replies:
    1
    Views:
    270
    Frank Kabel
    May 17, 2004
  4. Guest
    Replies:
    3
    Views:
    267
    Guest
    Jul 2, 2007
  5. zvkmpw

    Re: Address fields in columns...

    zvkmpw, Aug 14, 2012, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    283
    zvkmpw
    Aug 14, 2012
Loading...

Share This Page