+ Reply to Thread
Results 1 to 11 of 11

how to separate city state zip field into separate columns

  1. 02-23-2019, 03:02 PM #1
    djc225
    djc225 is offline
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    40

    how to separate city state zip field into separate columns

    I have a column labeled city, state, zip and I want to separate the three for import into a database. problem that I'm having is that the data is from numerous sources so I don't have a standard template to follow that I can see to create formulas on. some have multiple digit zip codes, some seem to have different spacing before or after commas, some city names have multiple words, ect.

    interested in hearing thoughts on how to clean this up.

    attached is the column that I'm working on.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-23-2019, 03:14 PM #2
    Logit
    Logit is offline
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,014

    Re: how to separate city state zip field into separate columns

    Use TEXT TO COLUMNS on the DATA tab up top.

    Choose SPACE as the delimiter.

    Text To Columns.jpg
    Last edited by Logit; 02-23-2019 at 03:18 PM.
    Reply With QuoteRegister To Reply
  3. 02-23-2019, 03:20 PM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: how to separate city state zip field into separate columns

    How about in D2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    C2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    B2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 02-23-2019, 03:27 PM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: how to separate city state zip field into separate columns

    Hi djc

    Here is how I'd do this problem. Attached in the file. I'd be using Power Query that has a Split from Right most delimiter. See comments in the worksheet.

    city state zip cleanup.xlsx
    Last edited by MarvinP; 02-23-2019 at 03:29 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 02-23-2019, 03:30 PM #5
    djc225
    djc225 is offline
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: how to separate city state zip field into separate columns

    Quote Originally Posted by MarvinP View Post
    Hi djc

    Here is how I'd do this problem. Attached in the file. I'd be using Power Query that has a Split from Right most delimiter. See comments in the worksheet.

    Attachment 612684
    thanks, I'll have to look into power query.
    Reply With QuoteRegister To Reply
  6. 02-23-2019, 03:30 PM #6
    djc225
    djc225 is offline
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: how to separate city state zip field into separate columns

    Quote Originally Posted by Logit View Post
    Use TEXT TO COLUMNS on the DATA tab up top.

    Choose SPACE as the delimiter.

    Attachment 612683

    Thanks. I tried doing this but then have many rows that needed manual editing due to city names having two, three or more words in the city name.
    Reply With QuoteRegister To Reply
  7. 02-23-2019, 03:32 PM #7
    djc225
    djc225 is offline
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: how to separate city state zip field into separate columns

    Quote Originally Posted by Fluff13 View Post
    How about in D2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    C2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    B2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    this is amazing. thank you. I'll have to look into these formulas and learn more about how you used them. Even seeing these results and having the formula to reference I think it will take me all day to understand the thought process here! Thanks again.
    Reply With QuoteRegister To Reply
  8. 02-23-2019, 03:54 PM #8
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: how to separate city state zip field into separate columns

    Please try at B2 drag to D2 and drag down

    =TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,", "," "),TRIM(C2&" "&D2),""))," ",REPT(" ",9)),9*(1+(COLUMN()=2)*9)))
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  9. 02-23-2019, 03:59 PM #9
    djc225
    djc225 is offline
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: how to separate city state zip field into separate columns

    Quote Originally Posted by Bo_Ry View Post
    Please try at B2 drag to D2 and drag down

    =TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,", "," "),TRIM(C2&" "&D2),""))," ",REPT(" ",9)),9*(1+(COLUMN()=2)*9)))
    thanks, I understand that. I was more thinking so if I want to recreate a similar formula in the future.
    Reply With QuoteRegister To Reply
  10. 02-23-2019, 06:01 PM #10
    Haluk
    Haluk is offline
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,150

    Re: how to separate city state zip field into separate columns

    Another alternative is using "Regular Expressions" in VBA as in attached workbook.
    Attached Files Attached Files
    Last edited by Haluk; 02-24-2019 at 12:26 PM.
    Reply With QuoteRegister To Reply
  11. 02-23-2019, 06:08 PM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: how to separate city state zip field into separate columns

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need to separate into different columns address, apt#, city,state, zip
    By pick44 in forum Excel General
    Replies: 5
    Last Post: 01-23-2018, 02:26 PM
  2. [SOLVED] Columns for Address, City, State in Separate Columns, Need to have CSV in one column
    By pick44 in forum Excel General
    Replies: 3
    Last Post: 07-11-2017, 12:43 PM
  3. Separate city state and zip
    By cape in forum Excel General
    Replies: 7
    Last Post: 11-16-2015, 07:48 PM
  4. Need Macro to transpose multiple rows to detailed columns and separate city, state, zip
    By taboucher in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-14-2012, 04:47 PM
  5. Help splitting addresses into separate columns for City, State and Zip
    By sherships in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 11:30 AM
  6. [SOLVED] I wish to separate city, state, and zip into 3 separate columns
    By Bob Woolbert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-11-2006, 01:45 PM
  7. [SOLVED] how do I separate 1 excel column to read city, state, zip columns
    By usndiv in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2006, 05:40 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1