Using SPLIT with ARRAYFORMULA for form responses in google sheets

[Shivani Shinde]

Hi,

I have column with values like 

1(current)

2(current) etc.

I want just the integer number 1,2,3... so on.

I am able to use split as

SPLIT(A2, "(current)")

But this does not work with google form responses since new row is inserted for every response. I found that ARRAYFORMULA can be used for applying formulas to responses, but for some reason, it isn't working.

I tried these:

1. 

=ArrayFormula(QUERY( SPLIT(E2:E,"(current)")))

2. 

=ArrayFormula(SPLIT(E2:E,"(current)"))

If anyone could help with any suggestions/ links to docs, that would be great!

Thank you.

[Riël Notermans]

Make a second tab (sheet) that you fill with the responsss using arrayformula OR query.

Then your arrayformulas for split will work.

OR you might even manage toncompletedly use the arrayformulas in a new tab (sheet)

Op do 5 dec. 2019 06:21 schreef Shivani Shinde <shivani...@alefedge.com>:

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ad238665-5e6c-40f7-9f05-a95391f08278%40googlegroups.com.

[CBM Services]

You can also change the form to give you just the numbers instead of the additional characters.

---

From: Riël Notermans
Sent: ‎2019-‎12-‎04 10:41 PM
To: Google Apps Script Community
Subject: Re: [Apps-Script] Using SPLIT with ARRAYFORMULA for form responses ingoogle sheets

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHjvmF71%3DYaFRYtKuZ4BvE9yqxV%3Dd5zcvdWcjyxLkVUaRY0%3Dkg%40mail.gmail.com.

[Michael O'Shaughnessy]

Just a suggestion... there is a Google Sheets add called CopyDown that solves your issue: https://gsuite.google.com/marketplace/app/copy_down/889269636541  It basically has you put the formulas in ROW 2 and when a new row is inserted it "copies" the formulas to the new row.

HOWEVER, I have found the solution Notermans suggests to be the way to go.  I pretty much leave the "Response" tab alone and do all my "number crunching" and "formual using" on a second tab.  You can easily use the "=QUERY()" function to get all the data from the response tab to the new tab.  And if you use QUERY, it will automatically update when new responses come in.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5de94c7f.1c69fb81.71534.0f63%40mx.google.com.

[Jacob Jan Tuinstra]

Hi,

Please try the following formula:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A:A,"\d+"),))

Regards Jacob Jan

[Joe Schmidt]

=arrayformula(SPLIT(E:E,", ")) works for me to separate values that are separated by a comma. You have to make sure that there are enough empty columns for the split to put the data.

What errors are you getting. Can you create and example spreadsheet?