Can someone help me with Excel?...

Discussion in 'Mac Apps and Mac App Store' started by slick316, Nov 28, 2006.

  1. slick316 macrumors 6502

    Joined:
    Sep 28, 2005
    #1
    I have Office for my mac, I use Word all the time, but suck at using Excel.

    I am trying to help a friend out with a task. He needs me to clean up an excel doc of his customer list. He exported it from Quickbooks. Its all messed up, and I got it cleaned up, but its still messed up, and the list is 3000+ customers long.

    I need only three columns, A for full name, B for street address, and C for city state and zip.

    Problem is, for some reason, I have a fourth column, D, and it has some of the info that should be in C (mainly the zip).

    First thing I need, is to find out how to merge columns C and D into one and make it the new C column, is this possible?

    Also, is there a way to auto capitalize text and add spaces to when there isnt one (for instance, lots of the addresses dont have a space after the comma in the address).

    Trying to do this the easiest way, its so much info. Any help would be appreciated.
     
  2. Stevez0r macrumors member

    Joined:
    Mar 19, 2006
    Location:
    New York City
    #2
    If you go into format cells you should see a option to merge cells. But if you merge to cells with info it will take the info from the left cell. Sorry but I don't know to merge two cells with info into one cell, hope this helps a little.
     
  3. rdowns Suspended

    rdowns

    Joined:
    Jul 11, 2003
    #3
    You can't merge two columns and not lose some data. Higlight the cells you want to merge, choose Format ->Cells->Alignment->Merge cells.

    You can highlight multiple cells by holding dowm the Apple key as you click the cells.

    Hope this helps.
     
  4. WildCowboy Administrator/Editor

    WildCowboy

    Staff Member

    Joined:
    Jan 20, 2005
    #4
    How about using Concatenate? In a cell in a blank column (say "E1"), type the following:

    =CONCATENATE(C1," ",D1)

    That should put the contents of C1, a space, and the contents of D1 all together in E1. Apply to the entire E column, then delete columns C and D.
     
  5. blodwyn macrumors 65816

    Joined:
    Jul 28, 2004
    Location:
    Portland, Oregon
    #5
    No, don't delete columns C & D. You will lose the data in the concatenated column. You could hide columns C & D instead
     
  6. WildCowboy Administrator/Editor

    WildCowboy

    Staff Member

    Joined:
    Jan 20, 2005
    #6
    Oh, crap...right. You can copy and paste the contents of Column E into Column F and then delete C, D, and E.

    To do this, copy Column E, paste to Column F, then on the little clipboard icon that shows up, choose "Values Only".
     
  7. atszyman macrumors 68020

    atszyman

    Joined:
    Sep 16, 2003
    Location:
    The Dallas 'burbs
    #7
    Or you could copy column E, and paste special... and select Values and it will take the text from column E and paste it instead of the concatenate formula.

    Then you can delete your unused columns.

    As for your comma situation, replace all commas with a comma space combo. Then do a search for comma space space and replace all of those with comma space. That should add a space after every comma, and the second step should eliminate all of the double spaces.

    Hope this helps
     
  8. slick316 thread starter macrumors 6502

    Joined:
    Sep 28, 2005
    #8
    concatenate (wth does that mean?) worked great!! You guys are great. Got it all done after fiddling with excel a little.

    Thanks again.
     
  9. EricNau Moderator emeritus

    EricNau

    Joined:
    Apr 27, 2005
    Location:
    San Francisco, CA
    #9
    "link together"
     

Share This Page