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.