Automatically "split text to column" using Google App Script

Question

I'm trying to figure out "if" I can automatically "split text to column" using Google App Script and the trigger of "on Form Submit". If yes, can someone help me figure out the solution to following problem? If no, can someone please help me figure out an alternate solution?

I have a google form that is being used to enroll in professional development workshops. The workshop name being submitted via a google form has a Unique ID# in it. I need to copy or separate that unique ID# into another column.

Sample Workshop Text (Every workshop will be in this format)

Chromebook 101 | GHS | 8:00 am | ID#100103

The above string will be in cell B1 and I need to automatically copy or separate the value ID#100103 into cell C1. We will always be able to target the ID# as the place where the separation happens.

I know how to do this manually using the "data>split text into column", but I need this to be done automatically on form submission.

Any ideas?

Answer 1

You don't need a script for this. Enter the following in C1:

=arrayformula(iferror(regexextract(B1:B, "ID#\d+")))

This will populate column C with Id numbers (where the data in B is available), leaving the rest blank. As more entries are submitted through the form, more Id numbers will be in column C.

Another version uses a second regexextract to drop the ID# part and keep only the digits:

 =arrayformula(iferror(regexextract(regexextract(B1:B, "ID#\d+"), "\d+")))

Answer 2

Google recently changed the way that the built-in split function works. It now is able to work with arrays, so a custom function is not needed any more.

Example:

Assuming that the

 =ArrayFormula(split(B1:B,"#"))

Note: In order to prevent large recalculation times, remove the blank rows or your data, or replace B1:B by FILTER(B1:B,LEN(B1:B))