yogi_Split A Set Of Numbers In Cells Of A Column Row By Row

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #776  Sep 23, 2012    www.energyefficientbuild.com. 

user Maia Gatuna said: (Regular expression as an alternative to split with ArrayFormula?
Hi!

I have been searching around for a while, I am quite new to google docs & excel so please bear with me.

I am creating a spreadsheet with a form to register the participation of people in a charitable raffle. In the form, people tell which numbers (from 000 to 1000) they want to reserve and buy. The idea is to allow the person to choose several numbers separated by space (e.g.: 45 68 900 789) up to a maximum of 10 numbers

My first idea was using SPLIT to separate the input into multiple cells in order to follow the calculations. Something like this:

=ARRAYFORMULA(IFERROR(COUNT(SPLIT(C2:C; " ")))

so that if C2 = 10 20 30 40,

the output would be D2 = 10    E2 = 20    F2 = 30   G2 = 40

BUT, as I read in this forum, split DOES NOT WORK with arrayformulas, this is a reported issue. I found as an alternative Regular Expressions, and as I am completely lost in this field, I copied a formula I found in another post. This formula split a list of numbers  separated by ";"

My question is> Could someone please tell me how to modify this formula so that it splits numbers separated by SPACES and not by ;?

See my spreadsheet. Sheet 1 is what I want to achieve, Sheet 2 is the formula with RegExpr that I found on this forum:

https://docs.google.com/spreadsheet/ccc?key=0AqmxSQjUJgp5dDVfaFFiamk1X1V6YUVsUTNmcmgwYlE

Many, many thanks in advance!

----------------------------------------------------------------------------------------

The SPLIT function does work with arrayformulas ... however it doesn't split all the components of the first row cell

so in your case it should work fine except that the first row will simply read Number ... see the solution in my following blog post: