How to Combine Multiple Answers Into One Column With CONCAT
If you had multiple records across different columns, how would you combine them into one? Rather than having to do this manually, you can use the CONCAT function. Find out how it works in this short blog by Happy’s Chief Happiness Officer, Henry Stewart.
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
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:
This will result, in the above example, in one ethnicity column.
- 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.
Keep informed about happy workplaces
Sign up to Henry's monthly Happy Manifesto newsletter, full of tips and inspiration to help you to create a happy, engaged workplace.
Learn the 10 core principles to create a happy and productive workplace in Henry Stewart's book, The Happy Manifesto.
Henry's Most Popular Blogs
- 8 Companies That Celebrate Mistakes
- 9 Benefits of Having Happy Employees
- 321Zero: How I Solved my Email Overload
- 16 Companies That Don't Have Managers
- 5 Big Companies Who Swear by Mindfulness
- 49 Steps to a Happy Workplace
- A Four Day Week? Let's Start With a Four Day August
- Google: Hire Great People and Give Them Lots of Autonomy
- Buurtzorg: No Managers, Just Great Care From a Nurse-Led Service
- 30 Steps to Joy at Work: Get More Done by Being Less Busy
Next Conference: 2024 Happy Workplaces Conference
Our Happy Workplaces Conference is our biggest event of the year, and we'd love for you to join us next year. Save the date for Wednesday 15th May 2024!
This will be a hybrid event — we will host up to 50 people in the venue at Happy's HQ in Aldgate, London, and up to 200 people online via Zoom. The speakers will be a mix of online and in person. However you choose to join us for 2024, there will be interaction, discussion, space for reflection and opportunities to network with others.
All tickets are currently half-price until January 2024.