MS Excel: How to Combine Values of Two Cells

July 26, 2013

New MS Excel users often wonder how they can combine values to two cells in a third cell. Learn how to use concatenate function and ampersand (&) operator to join/combine contents of two or more cells in MS-Excel.

MS-Excel is a spreadsheet software from Microsoft Corporation. It is generally distributed as part of Microsoft Office package (which also contains software like MS-Word and MS-PowerPoint etc.)

While using MS-Excel, at times we need a formula to join content given in two (or more) cells. If we accomplish this by manually joining two strings and paste the result in a third cell –it won’t solve our purpose because when the source strings will change –the combined string will not change automatically. Here we explain, how you can use a formula to automate this tiny task.

Combine Strings using Ampersand (&) Operator

The “&” operator in Excel is used to concatenate two strings. You can use it in a formula as below:

Let’s assume you have someone’s first name in cell A1 and last name in cell B1 and you want to place combined name in cell C1

Select the cell C1 (wherein you want to place the combined string)

Type =(A1&” “&B1) in C1 and press enter. Combined string will appear in cell C1. What this formula is doing is concatenating three strings. First it brings content of cell A1 and suffix it with a space (there is a space between double quotes) and then content of cell B1 are suffixed to it.

Space between double quotes has been used just to separate first name from last name. If you don’t want this, the following formula will also work equally well:

=(A1&B1)

Here the ampersand operator simply glue together the content of A1 and B1.

Combine Strings using CONCATENATE function

Excel also provides you with a function to combine two or more strings. You can place the following formula in desired cell:

=CONCATENATE(A1,” “,B1)

This formula will produce the same result as explained in above section concerning ampersand operator.

Question: How will you produce this format LASTNAME, FIRSTNAME ?

Answer: You can use any of the following method

=(B1&”, “&A1)

Or

=CONCATENATE(B1,”, “,A1)

This is how you can join content of two cells in MS-Excel. I hope this was useful for you. Please feel free to ask any questions you may have. I will be happy to try and help you. Thank you for using TechWelkin.

Lalit Kumar is the Principal Author and Founder of TechWelkin. He is a web explorer and he enjoys finding useful information on the Internet. He loves to put things together to create a bigger solution. Lalit is passionate about technology, languages and literature. You can contact Lalit via email (techwelkin [at] gmail [dot] com) or Facebook (facebook.com/techwelkin).