Comma separated values into different cells

1

Hey community! I seem to have encountered a solution for a moudule I’m trying to develop, but for that I need to split comma separated values into different cells.

I have read about FormulaMap(), and Split(), but I’m either unable to make them work, or they’re not what I’m looking for.

The way I want it to happen is very simple, but as I’m kinda new to coda maybe I’m missing something easy that could work for me.

I want to turn this:
image

Into this:
image

Any suggestion? I know it has to be easy, but I haven’t found a way to do it yet, thank you!

2 Likes
2

Hey!

If I understand it right, in Column 1 and Column 2 you have comma-separated lists of the same size, and you need to create rows where Nth item from Column 1 goes on the same row as Nth item from Column 2. Then you need to do it through a numbered iterator (so that you can iterate over numbers 1 2 3 to use when selecting Nth values from separate columns)

In your Example Comma table:

  1. Add a column Col1Split with formula thisRow.[Column 1].Split(", ")
  2. Same for Col2Split
  3. Add a button with a following formula:
Sequence(1, thisRow.Col1Split.Count()).FormulaMap(
  example.AddRow(
    [Column 1], thisRow.Col1Split.Nth(CurrentValue),
    [Column 2], thisRow.Col2Split.Nth(CurrentValue)
  )
)

If it were just one column and no need to associate Nth item from column 1 to Nth item from column 2, it would be as simple as

[Column 1].Split(", ").FormulaMap(
  example.AddRow([Column 1], CurrentValue)
)

where CurrentValue wouldn’t be the index from the sequence, but the actual item itself.

3 Likes
3

Thanks for answering fast and so well explained!

I have tried what you said and I don’t know if i did something wrong or what, but that’s what I get when i push the button

This is the actual setup:

image
image829×359 32.9 KB

And this is what I get as a result:

image
image608×642 17.5 KB

Have I done something wrong?

2 Likes
4

Yes, you’re referencing wrong things in your formula. When you say tableName.ColumnName you’re referencing all rows from a column from that table. You need to use thisRow.ColumnName to read the value of a specific cell from this row.

The button — I meant add it to the Example table (a button column). You can have one global button then which will press those individual buttons.

E.g., [example comma].Col1Split.Count() is 1 because [example comma].Col1Split returns not a list of values in the cell, but a list of cells in a column per row (in your case one cell) that then contains a list of strings within. Hence count is 1 (for one cell returned)

Updated the formula above to make this clearer (usually when you don’t type in thisRow in table context, it’s automatically assumed)

3 Likes
5

Ahh, I understand now. Thanks for the explanation, it may help me with other stuff in the near future!

also useful to know that ^, I’m new here and i didn’t understand what was I supposed to do, so I referenced the table, my bad.

Edit: it works perfectly, thank you a lot Paul!

2 Likes
6

Hey Paul! I wanted to see if you could help me with the formula you provided me a few weeks ago.

It has been very useful until, for some reason, it stopped working this afternoon. My theory is that Coda recived some kind of update that affected how that formula works.

I have loaded a backup of my document from a week ago, and the formula still doesn’t work.

I have been using it every day, even this morning, so I suppose they changed something that I’m not aware of.

If it helps, now the formula, as it has been for weeks, gives this error on “Current value”:
image

Ex. of what happens: The articles 1, 2, 3 should be copied in 3 different rows, instead, all three rows become article1, then article2 and then article3.

Let me know if you have a solution! I’ll keep trying to solve it in case you’re busy.

7

Hey!

Something similar happened to me too. Double-check whether your green bubbles correctly point to thisRow or CurrentValue. In my case, items that didn’t have the thisRow or CurrentValue prefix explicitly, they resolved to a wrong context and stopped working as expected.

Oh, and your formula generally looks wrong. You have your table mentioned twice before .AddOrModifyRows() and as a first parameter too — you do either but not both. Also, CurrentValue within AddOrModifyRows() is overwritten with currently edited row — I suggest rewrite the formula so that you only use AddRows(), and if you need nested CurrentValue, then a different approach would be required

1 Like
8

Thank you Paul, I’ll see what can I do with that :slight_smile: