up vote 2 down vote favorite
share [g+] share [fb]

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

link|improve this question
80% accept rate
feedback

2 Answers

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.

link|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
1  
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
show 2 more comments
feedback
up vote 3 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.