Combine text in Excel using CONCATENATE

One of the most basic functions you may want to perform on strings in Excel is combining two or more strings of text into one long phrase. This operation is called concatenation, and is easily accomplished using Excel's CONCATENATE function.

There are many reasons why you may want to combine text, but here are a couple of examples to get you thinking:

• You have First Name and Last Name columns in a customer database, and want to combine them together to get customers' Full Name;
• You have a table with branch information listed by city and state, and you want to combine the City and State columns into one Location column; or
• You have a list of employee names, and want to combine them together to form a unique E-mail address for each employee.

This list is just the tip of the iceberg; there are numerous reasons why you may want to use CONCATENATE in your spreadsheet, and it's one of the most basic (and useful!) string functions out there. Here's how to use it.

The basic CONCATENATE function

To combine two fields of text in Excel, use the basic syntax of CONCATENATE:

=CONCATENATE(string_1, string_2...)

The function will take your inputs and combine them into one single string. Note that you can include as many arguments as you want at the tail end of CONCATENATE; it will accept as many as you give it and combine them all as you see fit.

Here are a few examples of CONCATENATE in action:

=CONCATENATE("Sarah", "Smith")
Output: "SarahSmith"

=CONCATENATE("Sarah", " ", "Smith")
Output: "Sarah Smith"

=CONCATENATE("Boston", "MA")
Output: "BostonMA"

=CONCATENATE("Boston", ",", " ", "MA")
Output: "Boston, MA"

=CONCATENATE("ABC", "12", 3)
Output: "ABC123"

There are a couple of things to note here: first, CONCATENATE doesn't automatically insert any delimiters, like spaces or commas, into your final string. So, if you want things well-spaced, you'll have to include a space or a comma in your CONCATENATE function. Second, CONCATENATE converts numbers to strings before running, so including a number in quotes is functionally the same as using a number outside of quotes.

A shortcut for CONCATENATE: the ampersand (&)

Since CONCATENATE is such a frequently-used function, the designers of Excel have included a shortcut to access it: the ampersand symbol (&). Use the ampersand instead of CONCATENATE to combine strings of text like so:

=string_1&string_2...

Let's take another look at the above examples, this time using & in place of CONCATENATE:

="Sarah"&"Smith"
Output: "SarahSmith"

="Sarah"&" "&"Smith"
Output: "Sarah Smith"

="Boston"&"MA"
Output: "BostonMA"

="Boston"&","&" "&"MA"
Output: "Boston, MA"

="ABC"&"12"&3
Output: "ABC123"

Comments