+ Reply to Thread
Results 1 to 5 of 5

Data Separation from one cell to two

  1. #1
    Registered User
    Join Date
    11-01-2007
    Location
    Stavanger, Norway
    MS-Off Ver
    Excel 2013
    Posts
    22

    Data Separation from one cell to two

    I don't know if the title explains it, but I was given an Excel Directory type file (name, address, telephone etc.) which has been giving me issues with various exports because of how it's formatted.

    Specifically there is one names column with "Last Name, First Name" and then a "City, State Zip" column. I'm hoping that there is a way I can separate the "Name" column by extracting just the last names and copying them to a new column. Likewise I would like to be able to extract City, State and Zip and put them each in their own columns. Is this even possible to do?
    Last edited by [email protected]; 02-15-2009 at 04:05 AM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Data Separation from one cell to two

    As long as you have a common character seperating the fileds (like a comma) You can use Text to columns the is listed under the Data Menu

    Data Menu
    Text To Columns
    Delimited
    Tick the Delimited field that coresponds to the type of character that seperates each data type
    Finish button

    You will need a number blank columns to the right of your data
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data Separation from one cell to two

    Mudraker is right with Text To Columns.

    If you run into problems with the State/Zip portion, do the same thing and use "space" as the delimiter....based on your sample above.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    11-01-2007
    Location
    Stavanger, Norway
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Data Separation from one cell to two

    You guys are awesome. Just saved me a ton of time. Thanks a bunch.

    This problem has been solved, thanks.
    Last edited by [email protected]; 02-14-2009 at 10:46 PM. Reason: Marking Solved

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data Separation from one cell to two

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

+ 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