Tuesday, December 27, 2016

yogi_Array Formula For Splitting Row By Row Entries In Column A Of Sheet 'T1 Cores'

Google Spreadsheet   Post  #2099
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-27-2016
REGEXTRACT() function to split data in an array stopped working
Hi all,

I've been using the function string =TRANSPOSE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(" "&'T1 Cores'!S2:S26,"^"&REPT(" +[^ ]+",COLUMN(OFFSET(A1,,,1,25))-1)&" +([^ ]+)")))) for some time now to split data separated by spaces in cells in the range 'T1 Cores'!S2:S26 and it has stopped working properly now. All that is displayed is the first item in each of the cells in that range and nothing after the first space. Using the SPLIT() function with space as the delimiter works fine, but I have a large range of changing data so it's not practical to use SPLIT() since it can't be used in an array.

Any ideas? Here's a link to a sample sheet.

Thanks in advance!

Please note that I have been able to use SPLIT function for an array of values