+ Reply to Thread
Results 1 to 5 of 5

Split address from one cell to have street number/name and suburb/state/postcode separated

  1. #1
    Registered User
    Join Date
    08-04-2011
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Split address from one cell to have street number/name and suburb/state/postcode separated

    I'm after a formula or some formatting trick to split up the address in the attached sheet. As you can see, the addresses have come through from a database in one cell, instead of a separate cells for the street line and the suburb line, making it difficult to merge for mailing.

    eg. the address are coming through to the merge like this;

    Joe Bloggs
    3/119 newtown street sydney nsw 2001

    When i need them to look like this;

    Joe Bloggs
    3/119 newtown street
    sydney nsw 2001

    Thanking you in advance

    WeedMan
    Attached Files Attached Files
    Last edited by WeedMan; 05-27-2014 at 10:08 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Split address from one cell to have street number/name and suburb/state/postcode separ

    It will be difficult as there are no specific indicators of where the street name ends and the town/city name begins.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Split address from one cell to have street number/name and suburb/state/postcode separ

    My thoughts too. If you did not know, how you you decide how to break up these...
    71 GAN GAN ROAD ANNA BAY NSW 2316
    UNIT 1 33 PARRAMATTA STREET CRONULLA NSW 2230
    CARE DEFENCE REGION EST ORG & CORP SVCES INFRA - CNNSW RAAF BASE
    RED HILL YEOVAL NSW 2868

    Remember, to excel, they are all just random letters and numbers
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Split address from one cell to have street number/name and suburb/state/postcode separ

    This gets you mostly there. You would need to identify other street types as they crop up and add them to the spreadsheet. The ones showing errors you'll have to do manually.
    Mail list May 2014.xls

  5. #5
    Registered User
    Join Date
    08-04-2011
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Split address from one cell to have street number/name and suburb/state/postcode separ

    Gak you are a genius. I was beginning to think this was not achievable. You have saved me a lot of time. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split cells postcode from address
    By dsthome in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-10-2013, 02:15 AM
  2. Extract House Number from Street Address into new Cell
    By tamorgen in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-20-2011, 07:48 AM
  3. enter suburb return with postcode
    By lpratt in forum Excel General
    Replies: 3
    Last Post: 04-12-2011, 02:50 AM
  4. separate street address and street number
    By iwanttoplaywii in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-24-2011, 09:45 PM
  5. Suburb and Postcode List
    By crania in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2007, 09:25 AM

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