I have some raw data on Excel that is organized in a way that is not easily readable. For example, for a table showing which user likes which color:

        A      B        C       D       E
User | Red | Green | Orange | Blue | Yellow
A       0      1        0       1       0
B       0      0        1       1       0
C       1      0        0       0       1

So, user A likes Green and Blue, B likes Orange and Blue, and C likes Red and Yellow.

This is useful for us because I can use this to calculate frequency, but I also need the data in human readable form.

I would like to add a single column that says the colors that a user picked, like: User A - Green + Blue.

But I can't seem to do this with CONCATENATE(IF(A2=1, $A$1), +, IF(B2=1, $B$1)) and so on because the IF function doesn't work inside CONCATENATE. I also cannot add too many columns because my data has multiple columns already.

Is there a function I can use to turn this binary data into a clean string that can fit in on column?


You can do this without the CONCATENATE() function...


=TRIM(IF(B2=1;B$1;"")&IF(C2=1;" "&C$1;"")&IF(D2=1;" "&D$1;"")&IF(E2=1;" "&E$1;"")&IF(F2=1;" "&F$1;""))

The TRIM() function removes any excess spaces which may appear if the user likes Green but not Red.

  • I think you want to change your semicolons to commas. – Fezter Jun 6 '18 at 6:55
  • @Fezter no. Semicolon vs commas is a region restriction. For the formula to work here in Norway I need to use semicolons, for the formula to work where you are maybe you need commas. – Kevin Anthony Oppegaard Rose Jun 6 '18 at 7:11
  • Yeah I had to change to commas for it to work. I had no idea there were regional settings like that. – Fezter Jun 6 '18 at 7:53
  • Its annoying and took me a long time to come to terms with when I moved from the UK to Norway. But now im used to is :) – Kevin Anthony Oppegaard Rose Jun 6 '18 at 8:05

If you have Office 365 / Excel 2016, you can use the TEXTJOIN function in an array formula:

=TEXTJOIN(" ",TRUE,IF(B2:F2=1,$B$1:$F$1,""))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.