Turning binary data into string data in Excel

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?

2 Answers

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.