Thursday, September 27, 2012

Split and Manipulate text in a Cell and then Join it back

Question:

Lets say I SPLIT("1 2 3", " ") which results in the list {"1", "2", "3"}, how do I apply a function to each member of the list that mutates the current value and returns a list containing the mutated values?

For example, I might want to convert the list to integers and apply multiply(X, 2) to my list and get the list {2, 4, 6}

In javascript this would look like:
"1 2 3".split(" ").map(function(x) { return parseInt(x) * 2; });

I cannot figure out how to do "map()" in spreadsheet syntax.



Solution:

Let us say you have 
1,2,3,4,5
in Cell A1

Then put the following formula in any Cell where you want the results...
=join(",";arrayformula(multiply(split(A1;",");2)))

The above formula will give you the result
2,4,6,8,10
in a single Cell (where you insert the formula).



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


No comments:

Post a Comment