How to Combine Multiple Records Into One Column With CONCAT

The Happy helpline got a call yesterday from somebody wanting to combine a range of columns into one.

As a result of a survey, she had a spreadsheet with over a dozen columns for ethnicity:

This probably results from the survey being designed to allow more than one choice for this question, although they were looking for one answer. However, it is too late to redesign the survey.

There is a really easy way to combine them, using the CONCAT() function. This is designed to bring together text in different columns. You might, for instance, combine first name and surname to produce a full name, inserting a space in the middle: CONCAT(A2,” “,B2)

For the above example, on ethnicity, you simply enter every column which you want combined:

CONCAT(T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2)

This will result, in the above example, in one ethnicity column.

Related Blogs

• Using Absolute Cell References in Excel — What exactly IS an Absolute Cell Reference. Jonny breaks down the basics simply in this guide.
• How to Create a Pivot Table in Excel — Pivot tables are the perfect way to sort and analyse data in Excel. This two minute video takes you through the process of how to do so. 
• Save Time in Excel with Autofill — Autofill can save you lots of time, filling in data so you don't have to type it in yourself.