How to use the CONCAT function to Concatenate Text in Excel

A useful spreadsheet skill to learn is how to concatenate text in Excel. Perhaps you want to concatenate (join) a first name and a last name together to form a person’s full name. Or maybe you want to add a prefix to the beginning of a text value in your spreadsheet. All of this and more can be accomplished using Excel’s CONCAT function.

How Excel’s CONCAT function works

Excel’s CONCAT function can take up to 253 text arguments although it’s unlikely you’ll ever need that many. Each argument in the CONCAT function is concatenated together to output one string of text.

Take a look at the following function:

=CONCAT("John"," ","Lennon")

This function will output the text, "John Lennon". Notice, we had to include a second argument of a space (inside quotation marks) to make sure the first and last name didn’t run together.

Check out our Excel Lesson Videos to learn how to use more text functions in Excel.

CONCAT works with cell references too

The above example is over simplified to give you an idea of how the CONCAT function works. A better example would be to use the CONCAT function in Excel to concatenate a list of first names in one column and last names in another column into a list of full names in a third column.

The sample spreadsheet below shows our "before" spreadsheet:

CONCAT function in Excel

Now, we can use the following CONCAT function in cell C2:

=CONCAT(A2," ",B2)

If we copy cell C2 and paste it into cells C3-C6, we end up with our "after" spreadsheet:

CONCAT function in Excel

Be creative with CONCAT

The CONCAT function in Excel can do more than just put together first and last names. Feel free to be creative on how you use it. Here are a few more examples of things you can do using CONCAT (bracketed text would be replaced with cell references):

Add "Dr." to a list of attendees’ last names at a medical conference:
=CONCAT("Dr. ",[last name])

Create a single line for city, state and zip for a mailing label:
=CONCAT([city],", ",[state]," ",[zip])

Convert your numerical data into sentence form:
=CONCAT("The average of the numbers 14, 20 and 23 is ",AVERAGE(14,20,23),".")

One final note. While the CONCAT function in Excel can handle up to 253 text arguments, the resulting text string cannot be longer than 32,767 characters. If it is, the CONCAT function will return the #VALUE! error.

Comments are closed.

Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!