+ Reply to Thread
Results 1 to 5 of 5

Concatenate address lines of varying number

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-15-2011
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    117

    Re: Concatenate address lines of varying number

    Bump - any ideas?

  2. #2
    Forum Contributor
    Join Date
    06-15-2011
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    117

    Re: Concatenate address lines of varying number

    I have solved this with IF statements:

    =IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=3,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=4,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=5,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=6,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),5,0),OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),5,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),6,0)))))

    This has the following limitations:
    1. It only works in 2007 format (it exceeds reference limits on earlier versions).
    2. It only works with 2 to 6 lines of address - if I were applying this to other lists with more lines, it would not work. Is there a way of making this work with x number of lines?
    3. It is a mess!

    Any help or suggestions to improve this would be much appreciated.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by tone640; 08-08-2011 at 05:43 AM. Reason: Updated attachment

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1