I am trying to find a way to split the numbers from a cell with the following data in it:


With this data in a cell, in the adjacent 3 cells, I would like to see the results returned as thus 31 3 22

I have tried to use =iferror(arrayformula(split(D2:D,"(")),"") but obviously I have to do this 5 times which takes up multiple extra lines. I would then plan to fill the column to cover over 10,000 lines (possibly).

Any ideas? Is there a way of building multiple array formulae in one go?


One can extract multiple parts of a string with regexextract, for example

=arrayformula(iferror(regexextract(D2:D, "(\d+)\D+(\d+)\D+(\d+)")))

expects each string to contain three groups of digits, separated by non-digits, and places these groups in separate columns. So, 31(3)/22 becomes three entries: 33, 3, 22.

Notation: \d a digit; \d+ a group of 1+ digits; \D same for non-digits; () capturing parentheses: return the group of symbols within.

A more forgiving form of the regex is

=arrayformula(iferror(regexextract(D2:D, "(\d*)\D*(\d*)\D*(\d*)")))

which allows some groups to be empty: e.g., it will also split 34(22) to 34 22.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.