Separating Cells by Capital Letter.

Hi All.

I would be very grateful if someone could help with the following:

I am looking for a formula that separates cells by capital letter:

so If I have "BlueShoes", I would end up with cells containing "Blue" and "Shoes".

I need this for varying lengths of words, so for example "ReallyBloodyNiceBlueShoes" would be split to "Really", "Bloody", "Nice" and "Shoes".

I have found the attached file online, however, I can't work out how to adjust it for words with more that two words.

Positive feedback will be given for all help.

Thanks in advance.

Ryan.

16391_Splitting data in cells with capital letters.xls

Try running this macro when the cells are selected.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

I've extended the formulae out to column H, so that you can have up to 7 words, but I had to save the file as .xlsx because of the level of nesting (but you have XL2007, so that's okay).

However, if you have 2 or more words with the same initial capital letter it doesn't work properly, as can be seen in the example file.

Hope this helps.

Pete

I'd be very tempted to do this in VB, via a user-defined function.

Something like this, perhaps (not very neat, but it works):

Please Login or Register  to view this content.

With this in a module you could use:

=SPLITONCAPITALS("ILikeIceCreamCones",3)

And it would return "Ice", the 3rd capitalized word in the string.

If your data was in cell A1 then you could go with:

=SPLITONCAPITALS($A1,COLUMN(A1))

And drag across 10 columns (or however many words you expect to have) and have it return each word in sequence.

Probably easier than trying to do it in formula.