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.