INDEX(COLLECT()) function to pull partial data

Jayy S.

I'm using an INDEX(COLLECT()) function to pull data from cross sheet reference; however, is there a way to pull partial data from the reference cell?

For example, the reference cell is "Molecular Division" but I only want to pull "Molecular" into the target cell.

Archiduc

With the example you gave, you would use =LEFT([Primary Column]@row, FIND(" ", [Primary Column]@row)) (With Molecular Division being in Primary Column)

FIND will return the number of characters to get to the space between "Molecular" and "Division" and LEFT will return the number of characters starting from the left up to the space.

You can do that in another column (I recommend that) or you can try to add your index(collect) in place of the primary column (so twice). If you do that (I don't know if that works), I recommend adding an iferror before your index(collect) so yours errors do not become an issue.

the formula would look something like this =LEFT(iferror(index(collect()),""), FIND(" ", iferror(index(collect()),"")))