Splitting Text

[EvonS]

Using Office365

I need a way to split up text like this "Appropriate, sustainable housing, Financial stability" into columns. The problem is that when I use =TEXTSPLIT(A1, ",") it splits up "Appropriate, sustainable housing" in to 2 separate columns when it should be one. I cannot change that text to exclude the comma unfortunately. Is there a way to:

1. Get TEXTSPLIT to ignore the comma after "Appropriate"?
2. Is there a way to get TEXTSPLIT to split based on capital letters?

Note: I can't change the delimiter but I can place each option I want to split in quotes.

Thanks in advance for the help.

 

[EvonS]

First, I replace the combination of a comma/space/capital letter with a comma/space/capital letter/capital letter... then I split on the combination of a comma/space/capital letter. In other words, I double up the letter following a comma space and then split on the combination of a comma/space/capital letter. I need to do that because the delimiter (comma/space/capital letter) will be lost when the split occurs, so one of the two now doubled up letters will now remain after the split.

I would have never have thought of splitting based on a combination of characters. Thanks again. This helps my work a lot.

 

[Rick Rothstein]

I would have never have thought of splitting based on a combination of characters. Thanks again. This helps my work a lot.

Delimiters do not have to be single characters.