Concatenate text in a spreadsheet

The IT Directors assistant called me the other morning asking if I knew how to get text in separate columns into one column. She said she knew there had to be a way, but each time she would try to add them together she would get an error. Luckily I use to be okay with Lotus 1-2-3 and it still works in Microsoft Excel.

The key is the old ampersand (&).

You have various options on how you want things spaced, etc..

Shown are several examples.

The first is simply putting Cell A1 and Cell B1 together.

+A1&B1

The second simply puts a space between them by adding a space between quotes and the additional ampersand.

+A2&" "&B2

The third does the old classroom lastname followed by firstname.

+B3&", "&A3






Lots of times people will get them how they want them on the first one, copy it down the column and decide they don't want the original columns. Guess what, you delete them and you end up with the old reference error.






If you are simply trying to keep the text displayed by the formula in Column C just make sure you highlight the column where you just put your formulas and do a copy and then do a paste special right back over the top of it, simply selecting to paste values.






Once they are pasted as values, you can then remove the first couple of columns without getting the reference error. That is, if this is the result you are looking for.




Happy concatenating!