+ Reply to Thread
Results 1 to 9 of 9

Separate an alphanumeric cell into its components

  1. #1
    Registered User
    Join Date
    01-05-2010
    Location
    Tel Aviv , Israel
    MS-Off Ver
    Excel 2003
    Posts
    4

    Separate an alphanumeric cell into its components

    Hi,
    I need to separate the content of every address cell to the four separate cells : city, street, street number, zip code

    address cell examples:
    98 ironwood street rochester 48063
    chesterfield 55 street , Chicago 55660
    76 drexler , Bozman , 33087
    Collins 230, Austin
    etc
    etc

    I have a comprehensive city list available:
    Bozeman
    Chicago
    Austin
    rochester
    springfield
    Los Angeles
    etc

    order and location of numbers and texts in address cells is not fixed : 98 ironwood or ironwood 98, city name may appear first etc:
    98 ironwood rochester 48063
    rochester 48063 ironwood 98

    zip code is always 5 digit number
    street number is 3 digit or less
    Can anyone please point me in the right direction?
    Thanks,
    Ziv

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to seperate an alphanumeric cell into its components

    lots of manual copy paste would be required unles you have something seperating fieldsfor example is there always a ,(comma) between items?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-05-2010
    Location
    Tel Aviv , Israel
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: how to seperate an alphanumeric cell into its components

    Quote Originally Posted by martindwilson View Post
    lots of manual copy paste would be required unles you have something seperating fieldsfor example is there always a ,(comma) between items?
    there are usually no commas, just text and numbers. I suppose I could use a function to delete all commas to make it uniform.
    city name is one of a finite list of cities I have within the spreadsheet (perhaps 200 or so). Street name is any other text in the cell (no state or country in the cell). any 5 igit number is zip code. Any 3 or less digit number is street number.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to seperate an alphanumeric cell into its components

    attach an example of each variation,

  5. #5
    Registered User
    Join Date
    01-05-2010
    Location
    Tel Aviv , Israel
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: how to seperate an alphanumeric cell into its components

    address cell examples:
    98 ironwood street rochester 48063
    chesterfield 55 street , Chicago 55660
    76 drexler , Bozman , 33087
    Collins 230, Austin

    see attached spreadsheet

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to seperate an alphanumeric cell into its components

    try...
    Please Login or Register  to view this content.

  7. #7
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to seperate an alphanumeric cell into its components

    this works with the example
    Please Login or Register  to view this content.

  8. #8
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to seperate an alphanumeric cell into its components

    and if there is no street address
    Please Login or Register  to view this content.

  9. #9
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to seperate an alphanumeric cell into its components

    If you every come back added code to remove the comas
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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