Saturday, April 23, 2011

yogi_Using Split And Index Functions To Separate Entries From A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

HarbyNotts said:
I am wondering if the INDEX function can be used to give one single column out from this construct.
For example, if the data is in A2:
USA, Washington D.C. ¬The Netherlands, Amsterdam¬UK, London¬Italy, Rome
and the general function was something like this:
split(transpose(split(L2,"¬")),",")
could there be a way to just get the capital cities alone, using INDEX?

--------------------------------------------------------------------------------
In the following solution, I used the SPLIT function and the INDEX functions to accomplish the desired output. For some reasons which I can not explain now, using The Netherlands and Amsterdam, the name Amsterdam was being chopped off at d ... so that I will have to investigate later ... and in the mean time I substituted The Netherlands and Amsterdam with Japan and Tokyo which worked fine.



There is a problem with the SPLIT function involving certain alphabets. I have played around with a number of combination of words and letters ... although I have not been able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT function ... in the following examples through various sheets, it seems the names of Capitals of the countries in some cases get truncated after one or more letters. Of course the number of cases studied here is too small to derive a definitive conclusion of even this pattern.