With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Sunday, September 23, 2012
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, 2012www.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: