How to add "," between returned results
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)))
Best Answer
-
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)), ","))
Answers
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
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)), ","))
-
Thanks!!
-
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).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!