Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I have a couple thousand contact records in excel with columns with values of "First Name", "Last Name", and "Title" how could I combine those values into a column called "Contact" in the same row?

Microsoft Excel 2007

share|improve this question
up vote 3 down vote accepted

First create a new column, in that use the CONCATENATE function, like so:

=CONCATENATE( A1 , B1 , C1 )

This function basically sticks words togther, so if you've got "Mr", "John" and "Smith" in A1, B1 and C1 the result would be "MrJohnSmith".

This is easy to fix by adding extra spacers to the formula, to get "Mr John Smith".

=CONCATENATE( A1 , " " , B1 , " " , C1 )

Now just copy this down the rows, do a paste special to values, and you can delete the original columns.

share|improve this answer
+1 - Either way works! – JNK Aug 25 '10 at 13:42
Formula works... Having trouble with the paste step... Do I want values? Shouldn't I want formulas? – CT. Aug 25 '10 at 15:17
@CT - that's up to you (I stuck it in because I read that as being needed from your question, althought looking back I'm not sure why). Once you've got the formula spread down over all your rows (so every record is concatenated) you can leave it as it is OR copy/paste-special-values to remove the formulas. You'll probably need to do the last step if you're planning to import the files somewhere else, otherwise it is optional. – DMA57361 Aug 25 '10 at 15:26
He is saying Paste Special -> Values If you want to REPLACE the original columns with the combined column. If you paste the VALUES it will not matter if you delete the original cells. – JNK Aug 25 '10 at 15:26
I am actually talking about pasting the formula to the rest of the rows in the new column. Say my first new column "Contact" cell is A2. If I simply Ctrl-C on A2 and Ctrl-V on the next 20 cells down. I see the value of A2 in the pasted into cells. If I go into any of those cells and hit F2 to get into the formula, the correct one is there and if I hit ENTER it now displays the right value. Very strange. I do not want to hit F2 then Enter a thousand times. – CT. Aug 25 '10 at 17:46

Use string concatenation. you will need to add spaces, too.

Assuming your current cells are A1:A5, make A6 be

=A1 & " " & A2 & " " & A3 & " " & A4 & " " & A5

The & operator combines two strings.

share|improve this answer

Another way to do it, if you have multiple rows you want to copy:

  • Select rows you want and hit Copy.
  • Paste rows into Notepad.
  • Double click cell or hit F2 after selecting cell.
  • Paste rows in cell.

It should copy all rows into one cell.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .