Can someone help me with Excel?...

[slick316]

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.

 

[Stevez0r]

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.

 

[rdowns]

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.

 

[WildCowboy]

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.

 

[blodwyn]

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.

No, don't delete columns C & D. You will lose the data in the concatenated column. You could hide columns C & D instead

 

[WildCowboy]

No, don't delete columns C & D. You will lose the data in the concatenated column. You could hide columns C & D instead

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".

 

[atszyman]

No, don't delete columns C & D. You will lose the data in the concatenated column. You could hide columns C & D instead

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

 

[slick316]

concatenate (wth does that mean?) worked great!! You guys are great. Got it all done after fiddling with excel a little.

Thanks again.

 

[EricNau]

concatenate (wth does that mean?)...

"link together"