yogi_Using SPLIT And REGEXREPLACE 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?
--------------------------------------------------------------------------------
Here I have used SPLIT and REGEXREPLACE functions and with the limited number of test runs I do get correct desired results.
I had mentioned in my earlier post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-index-functions-to.html
I had only partial success ... as shown through various sets of sample runs shown in various sheets of that post
Then in the following post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-replace-functions.html
I had used the SPLIT and REPLACE functions to accomplish the desired output. I used this solution to run through all the sample runs that I had conducted in use with INDEX and REPLACE functions ... and these all seemed to give the correct desired result.



There was a problem with the use of the SPLIT and INDEX function combination involving certain alphabets -- I played around with a number of combination of words and letters ... and I was not able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT and INDEX function combination -- in the examples through various sheets the names of Capitals of the countries in some cases had gotten truncated after one or more letters.
However, the use of combination of SPLIT and REPLACE functions did give correct desired results -- I had only made a limited number of sample runs, so making more runs may be needed to know for sure ... but so far it yielded the correct desires results.
And now with the use of SPLIT and REGEXREPLACE functions I also seem to get correct desired results.

yogi_Using Split And Replace 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?
--------------------------------------------------------------------------------
Well HarbyNotts, as I delineated in my earlier post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-index-functions-to.html
I had only partial success ... as shown through various sets of sample runs shown in various sheets of that post

In the following solution, I used the SPLIT function and the REPLACE functions to accomplish the desired output. I used this solution to run through all the sample runs that I had conducted in use with INDEX and REPLACE functions ... and these all seem to give the correct desired result.



There was a problem with the use of the SPLIT and INDEX function combination involving certain alphabets -- I played around with a number of combination of words and letters ... and I was not able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT and INDEX function combination -- in the examples through various sheets the names of Capitals of the countries in some cases had gotten truncated after one or more letters.
However, the use of combination of SPLIT and REPLACE functions does give correct desired results -- I have only made a limited number of sample runs, so making more runs may be needed to know for sure ... but so far it yields the correct desires results.

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.