Use JOIN/COLLECT to return the first x number of characters

Lucas Rayala

I am using JOIN/COLLECT to return a list of items matching a criteria is a separate sheet. Items are returned in a single cell. The formula I'm using is:

=JOIN(COLLECT({Item Number}, {Date_Complete_Helper}, ="No", {ExtTestHelper}, ="No"), SUBSTITUTE(Lots39, "-", ""))

(the substitute is used to provide a line break between each returned item within the cell)

I would like to keep only the first 3 characters of each returned entry. For example, I want List 1 (below) to look like List 2 (below):

List 1:

Apple

Dog

It

Faster

List 2:

App

Dog

It

Fas

I can't get the LEFT function to work here. The formula won't parse if LEFT encloses COLLECT, and if I use LEFT to enclose the entire function, I only get the first characters of the first line (which makes sense, but I thought I would try). Any thoughts? I know I could create a helper column for {Item Number} but I'm doing this for many many columns and I'd rather not muddy that sheet up. I'd rather use a formula.

Mark Cronk

Hi Lucas,

I don't think it can be done with just a formula. You need LEFT and it doesn't work inside COLLECT, as you already found.

I'm curious to see if someone else can find a very creative solution to your puzzle.

Mark

Paul Newcome

A couple of notes...

When I have helper columns that I don't want cluttering up the sheet, I move them to the far right and then "Hide" them. They will still function the same way, but they aren't visible.

The function to insert a line break without having to use that reference cell is CHAR(10).

=JOIN(COLLECT(.........................), CHAR(10))

Finally... There are ways to parse data out based on a delimiter across multiple columns in a single row (helper columns that can be hidden). The setup is a little tedious, but it can be done and will operate rather smoothly once it is setup. Your particular situation would require taking the parsing formulas and just wrapping them in the LEFT function then finally joining them back together in your displayed column. If you would be interested in that solution, let me know. I'd have to do some digging into my notes, but I know it is out there somewhere.

Mark Cronk

Hi @Lucas Rayala ,

I think your best solution is to insert a helper column as your {Item Number} range. Extract the Left 3 into that column and then collect and join them.

Mark

Lucas Rayala

Thanks Mark, I'm trying to avoid that helper column, as I noted in the original question. Any ideas on how to do this with just a formula?

Mark Cronk

Hi Lucas,

I don't think it can be done with just a formula. You need LEFT and it doesn't work inside COLLECT, as you already found.

I'm curious to see if someone else can find a very creative solution to your puzzle.

Mark

Lucas Rayala

I ended up creating the helper column! Alas. The one idea I had that I couldn't execute on was to have my JOIN/COLLECT return my results with a identifiable delimiter (such as "@" or something that wouldn't be in my data source), the cut up and recompile the list in another cell, taking the first 20 characters after the @. However, I can't wrap my brain around that right now.

Mark Cronk

Good morning Lucas,

Please message me when you find that solution. I know you'll figure it out someday. It's amazing what you can do with creativity and a handful of Smartsheet functions.

Happy New Year!

Mark

Paul Newcome

A couple of notes...

When I have helper columns that I don't want cluttering up the sheet, I move them to the far right and then "Hide" them. They will still function the same way, but they aren't visible.

The function to insert a line break without having to use that reference cell is CHAR(10).

=JOIN(COLLECT(.........................), CHAR(10))

Finally... There are ways to parse data out based on a delimiter across multiple columns in a single row (helper columns that can be hidden). The setup is a little tedious, but it can be done and will operate rather smoothly once it is setup. Your particular situation would require taking the parsing formulas and just wrapping them in the LEFT function then finally joining them back together in your displayed column. If you would be interested in that solution, let me know. I'd have to do some digging into my notes, but I know it is out there somewhere.

Lucas Rayala

Hi @Paul Newcome -- thank you for the tip regarding CHAR(10). That's so much cleaner. Regarding the helper columns, yes, I do put them to the right and hide them; however, our spreadsheets do a lot of lifting and it's easy to overwhelm things. I just like to avoid leaning on helpers if I can, but sometimes it's not worth it.

I contemplated doing that parsing you mentioned, but decided this issue earned a helper column in the main spreadsheet. All the workarounds are too tedious. Thanks for the advice!

Paul Newcome

Happy to help. 👍️