Excel Help

Discussion in 'Mac Apps and Mac App Store' started by rdowns, Apr 1, 2009.

  1. rdowns macrumors Penryn

    rdowns

    Joined:
    Jul 11, 2003
    #1
    I'm setting up lists that will be uploaded into a database (comma delimited). The spreadsheet fields are name, address, city, state, zip, phone etc.

    A colleague did a crap load of these but copied the city, state, zip into a single field.

    Is there a way for me to convert that to the fields above without copy and pasting a million times?
     
  2. KC2 macrumors regular

    Joined:
    Feb 29, 2008
    #2
    So is it like:

    City1, State1, Zip1
    City2, State2, Zip2
    City3, State3, Zip3

    All of these are under Column A, but in separate rows? And they ARE separated by commas? Have you tried the Text to Columns feature?

    As you may know Office 2003, 2007, Office 2008 for Mac are all a little different in their own regard, but the function is pretty much the same:

    http://office.microsoft.com/en-us/excel/HA011498511033.aspx
     
  3. sushi Moderator emeritus

    sushi

    Joined:
    Jul 19, 2002
    Location:
    キャンプスワ&#
    #3
    If I understand what you want to do, you could set up a Macro or maybe a complex IF statement, to do this.

    Basically parse the string (cell contents) into three parts. Then save each part in three different columns.

    When done, copy and paste there they go by content only with no equations.
     
  4. Consultant macrumors G5

    Consultant

    Joined:
    Jun 27, 2007
    #4
    General idea:

    Copy the data from the field to text editor

    Use Search and Replace in text editor to replace ',space' with 'tab'
    or ',' with ',tab'

    Paste back to spreadsheet.
     
  5. rdowns thread starter macrumors Penryn

    rdowns

    Joined:
    Jul 11, 2003
    #5
    I tried text to colums but i get a message saying that cannot change part of merged cell. :confused:
     
  6. KC2 macrumors regular

    Joined:
    Feb 29, 2008
    #6
    You may want to give the other responses a try too, but if you stay with TTC...

    It sounds like you might be selecting more data than you need to. Select just the data you want to split up. All the data should be in consistently formatted cells (no merged cells). So, for example, if I were to split this data here: http://www.federalreserve.gov/releases/h15/data/Business_day/H15_PRIME_NA.txt

    I would select starting with the word DATE (where the table starts) down to whatever row I want. I copy/paste this into Excel. Highlight this same data (the data you want to breakdown), then select the TTC feature. Choose Delimited, then choose Comma as the Delimiter, and that's pretty much it. It then breaks that comma separated data into two separate rows.

    Hope this helps some!
     
  7. sushi Moderator emeritus

    sushi

    Joined:
    Jul 19, 2002
    Location:
    キャンプスワ&#
    #7
    Check out the string functions like Right, Left, etc.

    Maybe you can chop off parts to get what you want saved into new cells.

    I would probably parse from right to left because you know the zip code length, and you know the state length assuming that it is abbreviations. What's left is the city.
     
  8. rdowns thread starter macrumors Penryn

    rdowns

    Joined:
    Jul 11, 2003
    #8
    Thanks for the suggestions. Going to try these.
     
  9. bartelby macrumors Core

    Joined:
    Jun 16, 2004
    #9
    In the city column use:

    =LEFT(A1,FIND(" ",A1)-1)


    in the state column use:
    Code:
    =MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256),FIND("^",SUBSTITUTE(A1," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256),FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2)

    and in the zip use:

    =RIGHT(A1,FIND(" ",A1)-1)
    Picture 2.png
     
  10. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
    #10
    I'm sure you've got enough to go on here, but I would save as a CSV and then import it again; it should allow you to separate the fields if they have commas in them.
     
  11. bartelby macrumors Core

    Joined:
    Jun 16, 2004
    #11
    Ha! I some how missed that the stuff had commas in! :eek:

    I spend most of my working day arseing about with huge datasets that people have messed up. By the end of the day I'm blind to commas...
     

Share This Page