Hey guys,
I made some explanation in this video I hope you guys can share your thoughts
Thanks in advance!
1 Like
Hi Mohamad, great question. I think you should find it easy by using the add row feature, you may need to use a compound action with it or if your using a form just designate the columns accordingly.
Are you familiar with the SPLIT formula? That combined with an arrayformula will allow you to split the string by the pipe (|) delimiter into separate columns.
Arrayformulas cannot correctly use the split action. You would need a script instead.
It would be great if it did work it would look like this:
=ARRAYFORMULA(SPLIT(B2:B,"|")
But it would split the text into the columns directly to the right of column “B”.
And everytime you did this it would remove previous splits in rows above.
A script can take the cell values copy them to the correct location while keeping original data intact and then perform the split on the column the data was added to recently. With doing this you do not risk data be overwritten and it would perform correctly regardless.
Is that the same array function?
Yes, it’s an arrayformula in cell F2.
Everytime I have attempted to use that on delimiters other than common delimiters it messes up. But hell, if it works then it works. There you go, paste formula in column T and your done.
2 Likes
Thin, write the formula using the “|” as the delimiter. whenever I try to use delimiters like that it does not work properly.
1 Like
Nice, even after I copied and pasted it over it didn’t work.
I went to change my spreadsheet settings to UK then back to the states and it worked…I don’t know if I was actually on the states…
1 Like
Hmm, that’s weird. I’ve seen semi-colon(;) and comma(,) swap roles based spreadsheet locale, but I never seen issues with pipe. I’m used to working with pipe delimited data where I work, but I suppose it’s not a common delimiter for most people.
3 Likes
I actually have a iventory list of vehicles at work that I run a a script same time everyday that does this delimiter process because no matter how many times I attempt to automate it, it doesn’t work. So I just wrote a script using the same function wtih no issues afterwards.