Excel spreadsheet help, please - DPChallenge Forums

Nikon AF-S DX Nikkor 35mm f/1.8G

09/16/2014 07:30:54 PM · #1

I have an existing spreadsheet (email address, full name, city, country).
Now I need to add another 400 names from a different spreadsheet to that list, but the names are split into two columns - first name and last name.

How do I go about combining those two columns so it fits the existing format?

Thanks for your help.

damjanev

Nikon D3200

09/16/2014 07:44:24 PM · #2

Assuming that columns A and B have your first and last names respectively, use the formula =A1&' '&B1 to concatenate the texts from cells A1 and B1 and add a space between them. Then use fill (grab the little mark on the bottom right corner of the cell and drag) to apply the same formula to the next rows.

ETA:
Maybe you should use double quoted space in the formula instead of single quoted. Give it a try.

Message edited by author 2014-09-16 19:45:25.

MadMan2k

Sony Alpha a6400

Tamron 70-180mm F/2.8 Di III VXD

09/16/2014 07:49:41 PM · #3

Trying to visualize this in my head - that would require her to paste the new names into columns A & B first, right?

Sounds like probably the easiest way - I know there's a way to pull from another worksheet (tab) using the ! sign in the formula, but pasting it into the same worksheet would be easier assuming the data didn't go away when she deleted the pasted columns.

How would you go about preserving data that was pulled with a formula after you deleted the source?

I deal with Excel for work fairly often but it's all basic stuff, slowly getting better at it but it does so much that I am clueless about.

09/16/2014 07:51:00 PM · #4

Yikes..... that sounds hard! But I'll have a go, thanks.

In my case, column B is the first name, column C is the last name.

I can get column D to combine B and C, but can't get a space in between frist and last name.
Every time I add something in the formula between them, it no longer recognizes C1 as a cell destination.

I have tried " and " " and ' and '' and all sorts of other things to create a space...... no luck yet.

Message edited by author 2014-09-16 20:01:45.

09/16/2014 08:05:43 PM · #5

Yipppeeeeeeeeeeeeeeeeeee and BINGO !!!!!!!!!!!!!!

Thanks for giving me the correct term - I found it!!! //spreadsheets.about.com/od/excelfunctions/qt/07-concatenate.htm

It now works a treat, thank you!!!

Canon RF 24-70mm f/2.8 L IS

09/16/2014 08:09:52 PM · #6

Aaaaarrrrrggggghhhhh no, it doesn't work!!!!

Now I have the problem Jon referred to...... I can't move the combined data, it works only as a formula next to the two original columns!

kirbic

Canon EOS R5

09/16/2014 08:13:07 PM · #7

Originally posted by Beetle:

Aaaaarrrrrggggghhhhh no, it doesn't work!!!!

Now I have the problem Jon referred to...... I can't move the combined data, it works only as a formula next to the two original columns!

Copy the concatenated data, then instead of "paste" use "paste special" and choose "values."