How to Concatenate Text in a Spreadsheet
June 10th, 2008
If you have text in two or more columns that you wish to join (concatenate) in another column, this can be easily done by creating a formula that utilizes the ampersand (&). I have found this to work in Microsoft Excel, OpenOffice’s Calc, and iWork’s Numbers.
For our example, we will use a scenario where a list of users’ first and last names are given to you and your boss wants them to be in a single column instead of two.
You could take the time to copy and paste the names together in a column, but there is a much easier way. We can go to C2 and input the formula: =A2&” “&B2 we would then get the concatenated string of Bob Smith in the new cell.
We could use =B2&”, “&A2 and produce Smith, Bob in the new cell.
Copy the formula to the rest of the cells in the column to concatenate the desired cells.
June 11th, 2008 at 2:58 am
Very cool. I always have to look this up. Will bump this up to the front page so everybody can enjoy.
June 11th, 2008 at 4:11 am
Cool, but how do you split a cell in two?
June 11th, 2008 at 3:40 pm
@Tor
In order to split a cell (or group of cells) containing some text(s)in a single column
1. Select the cells in the column,
2. In the menu bar: click at ‘Data’ and then select ‘Text to columns’
3. Choose the criteria how you want to distribute the text in two columns (this page is self-explanatory!)
4. Click OK and voila!
Keep in mind that you can do it column-wise only.
June 11th, 2008 at 8:29 pm
@Kousik Thanks for the tip, although you cost me my next blog post on the subject.
June 11th, 2008 at 10:37 pm
[…] this can be easily done by creating a formula that utilizes the ampersand &. I have found thishttp://blogs.tech-recipes.com/shamanstears/2008/06/10/how-to-concatenate-text-in-a-spreadsheet/Microsoft Sues Clearwater Store Over Pirated Software The Tampa TribuneMicrosoft Corp. has filed a […]
June 18th, 2008 at 7:15 am
It is very useful article. I am using excell for the past 20 years for preparing MIS. This is a very useul tips. Thanks once again.
June 18th, 2008 at 10:06 am
No, it did not work for me.
June 24th, 2008 at 7:31 am
Don’t forget to copy the contents of column c then click “edit”, “paste special”, “values” into column d. Otherwise whenever you change/delete any info in a or b, your data in c is manipulated. (You can then delete c all together if desired)
July 1st, 2008 at 6:05 am
That was exactly the way I solved the problem… until I learned of the CONCATENATE function!
So, for the two examples in the original post above, you would enter:
Eg 1.
=CONCATENATE(A2,” “,B2)
Eg 2.
=CONCATENATE(B2,”, “,A2)
@Kousik
Thanks, I never knew you could do that! Just like ‘text-to-table’ in Word really!
— rc;)
July 16th, 2008 at 8:10 am
[…] this can be easily done by creating a formula that utilizes the ampersand &. I have found thishttp://blogs.tech-recipes.com/shamanstears/2008/06/10/how-to-concatenate-text-in-a-spreadsheet/Excel Joining Two Text Columns First Name Last Name with ConcatenationAn excel formula to join two […]
July 31st, 2008 at 11:02 am
[…] the ampersand &. I have found this to work in Microsoft Excel, OpenOffice??s Calc, and iWork?http://blogs.tech-recipes.com/shamanstears/2008/06/10/how-to-concatenate-text-in-a-spreadsheet/Microsoft Excel : Formula editing improvements Part 3: new functionsIn addition to improving the […]
January 7th, 2009 at 7:37 am
very nice post…