Great personal satisfaction in wrangling built in formula to do this but also a Google Apps Script demo as well (the array/cell detection in the script solution is one I'll probably use again. Is there a better way of doing it?)
12 plus ones
Shared publicly•View activity
View 14 previous comments
- Bruce McphersonModerator+marjolein hoekstra it's just a trick to generate a list of sequential numbers from 1to n, so row(x1:x5) would create an array of numbers 1 to 5... Meaning the row numbers of the given range. The use of indirect allows the substitution of 5 with the variable length of the string. Normally I'd use row (1:5) but a bug in google docs throws a circular reference error if its used in a row that overlaps with the formula. I chose column x at random.Aug 2, 2013
- Thanks once again. I hesitantly admit I still badly lack understanding, guys. I actually find this rather awkward, because you obviously put in so much effort and I don't know how to adapt the formula so that it works with my own sheet without affecting adjacent columns. It seems I still need to learn what the functions do that you use. I just don't know how or where to start. I'll keep trying and will let you know once I can wrap my brain around this little beast.Aug 6, 2013
- Bruce McphersonModerator+1feel free to post your specific issue .. I'm sure that any of or or I could point you in the right direction..Aug 6, 2013
- Thanks for the offer, Bruce.Aug 6, 2013
- Update: good news - the formula works for me now. Apparently I initially had made a tiny mistake in addressing the right columns, causing the adjacent columns to be modified instead. That's all fixed now.
I'm still puzzled and awed by the smart magic of the formula (in particular how you are able to parse a word character by character without using a loop construction). Call it lack of experience. When the occasion arises I'll go play and adapt the formula, hopefully gaining more insight.
Did I say thank you yet?Aug 7, 2013
- Bruce McphersonModeratorGlad you got it working. There is an 'implied' loop. The arrayformula() provokes multiple evaluation iterations and the row() trick generates a different index for each quasi-loop iteration. The use of indirect allows us to set the number of iterations to the length of the input string. Concatenating the result of each iteration is the final step.Aug 7, 2013