Remove Blank Values from Array? ArrayCompact()?

Hi there!

I’m having trouble finding a way to remove blank values from an array. In Airtable, for example, there’s an aggregate function called ARRAYCOMPACT(). This removes any blank or null values from the array before displaying it. Is there a Coda equivalent?

Thank you!

Hi @Alli_Alosa,
there is not a specific function to accomplish this.

You can obtain the same effect with [YourArray].Filter(CurrentValue!= " ")
(Prepend also Unique() to Filter(), if this is pertinent to your needs)

As a rule of thumb, if you could share your use-case - or a similar example - would surely help to have the whole picture: sometimes the better solution could hide somewhere else :wink:

1 Like

Thank you, @Federico_Stefanato! That should do the trick :slight_smile:

Here’s just one example about why I’m asking… it’s obviously very easy to accidentally add blank rows to tables. Some less tech-savvy people might not even realize when they’ve added something extraneous. I want to write my formulas to account for these scenarios so that I don’t end up with a confused client asking me why a field contains something like “, , , ,Jonathan” instead of just “Jonathan”. :slight_smile:

You can also use IsNotBlank() instead of !=" " ([YourArray].Filter(CurrentValue.IsNotBlank())
Additionally, you can use automation to to delete empty rows.

2 Likes

Right, @Asaf_Dafna. Much more elegant: agree.

.trim() seems to be working for me.

1 Like

I think we came out with different solutions, but I guess that - for this use case - the best one is actually the combination of @Asaf_Dafna’s and @Marc_Fitt’s together; i.e. Trim().IsNotBlank().

Let’s consider this TestArray: List("An, ,array ,with, ,blank, ,spaces, ,and,,,empty,,values,,").Split(",").
Then
[TestArray].Filter(CurrentValue.Trim().IsNotBlank()) would take care of both empty strings and blank (null) values.

2 Likes

Yes that would work too. I add the .trim at the end of the filter like this

I’m not sure if this slow things down in any sort but I feel not putting it in the filter help not searching for the empty one and simply get all the results faster. Not sure about this, that’s just a theory.

I could be wrong (not in front of my computer, right now) but I think that Trim() does affect a single string, and does not recurse in an array of strings.

If you try List(" one , two , three ").Split(",").Trim() I think is not trimming the individual elements
(does it? :thinking:)

It’s trimming the result from what I see. It does work for me in most cases so far haha