How to add "," between returned results

tbelly50

I'm using the below formula to pull all TR numbers (TRnumber) associated with specific IDs (LIMSID) from my master spreadsheet, but I'm not sure how to add a "," between each TR#. All suggestions appreciated!

Thanks!

=IF(ISBLANK(JOIN(DISTINCT(COLLECT({TRnumber}, {LIMSID}, [LIMS ID]@row)), CHAR(10))), "", JOIN(DISTINCT(COLLECT({TRnumber}, {LIMSID}, [LIMS ID]@row)), CHAR(10)))

Brian_Richardson

Looks like you're already using JOIN to pull them together. Instead of using CHAR(10) put "," instead in the tail end of your JOIN.

=IF(ISBLANK(JOIN(DISTINCT(COLLECT([TR#]:[TR#], [LIMS IDs]:[LIMS IDs], [LIMS ID]@row)), ",")), "", JOIN(DISTINCT(COLLECT([TR#]:[TR#], [LIMS IDs]:[LIMS IDs], [LIMS ID]@row)), ","))

Kleerfyre

So you already have it doing a space, just add the , to the space in the "". It would look like this: ", ". See if that works for you.

Brian_Richardson

Looks like you're already using JOIN to pull them together. Instead of using CHAR(10) put "," instead in the tail end of your JOIN.

=IF(ISBLANK(JOIN(DISTINCT(COLLECT([TR#]:[TR#], [LIMS IDs]:[LIMS IDs], [LIMS ID]@row)), ",")), "", JOIN(DISTINCT(COLLECT([TR#]:[TR#], [LIMS IDs]:[LIMS IDs], [LIMS ID]@row)), ","))

tbelly50

Thanks!!

Paul Newcome

That CHAR(10) delimiter in your original formula is a line break. If you use the wrap text feature with your original formula, each TR# will be on its own line (within the same cell).