0

I have a spreadsheet (exported from a database, so I'm stuck with the output format I'm given) with fourteen columns to indicate which of fourteen decks have been checked. (And yes, the numbers aren't stored as numbers.) Spreadsheet columns with deck numbers

I'd like to combine them all into a single cell with the deck numbers separated by commas. Normally I'd just use a series of &", "& elements, but of course that won't work when it's an uncertain number of decks. I could also use a series of IF functions, but that's going to be pretty cumbersome.

As an example, I'd like the top row in the image to be summarised as "1, 2, 3, 4, 5, 6" rather than "1, 2, 3, 4, 5, 6, , , , , , , , "...

Is there a simpler, easier and/or better way of doing it?

Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • 3
    Look up TEXTJOIN and if you don't have it yet in your version of Excel, there's a UDF equivalent here on SO. – BigBen Jan 29 '20 at 14:25
  • 1
    here is one of the udf that mimic textjoin: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Jan 29 '20 at 14:28
  • Such a clean and simple solution - `TEXTJOIN` did the trick perfectly. Would you like to post it as an answer? – Andrew Perry Jan 30 '20 at 07:55

2 Answers2

1

There's comments suggesting TEXTJOIN. However, you can also do this with CONCAT, COLUMNS, LEFT, and a bit of array formula power. (CTRL + SHIFT + ENTER)

= LEFT(CONCAT(A2:J2 & ","), ((COLUMNS(A2:J2) - SUM(1 * (A2:J2 = ""))) * 2) - 1)

Where AJ:J2 is the row you're attempting to comma split.

A quick explanation:

  1. Concatenate each cell with ",". Then concatenate all of those together.
  2. Get the total number of columns in the row, and subtract the count of columns which are blank.
  3. Multiply that number by 2 to account for the commas, and subtract one to remove the last one.
  4. Take the substring up to the number of characters determined in 3.
delyeet
  • 168
  • 9
1

In AA2, formula copied down :

=SUBSTITUTE(TRIM(N2&" "&O2&" "&P2&" "&Q2&" "&R2&" "&S2&" "&T2&" "&U2&" "&V2&" "&W2&" "&X2&" "&Y2&" "&Z2)," ",", ")

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Thanks! I went with `TEXTJOIN` on this occasion, but it looks like `SUBSTITUTE` and `TRIM` will be useful functions to know in another part of my spreadsheet. – Andrew Perry Jan 30 '20 at 07:58