Splitting Cells by Capital Letters

Dear Paul

Lets assume on Cell A1 you have the word that needs to be split.

You were almost there!! On cell B1 you enter the following function but you need to make sure that you use CTRL+SHIFT +ENTER after entering the function:

=LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

This will only provide you "Ice" as the result on Cell B1.

Then you click on Cell C1 and use the function:

=REPLACE(A1,1,LEN(B1),"")

followed by CTRL +SHIFT +ENTER

This should give you the result as "Cream"

I hope this helps. I am attaching an excel file which should help you to understand the formula bit clearly. Please observe the functions in Column B and C. You can replace your exsiting data in column A and see if it works!!

I hope this helps.

Please mark this post as resolved if it has answered your query!!

Many thanks

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000/2003
MCAS Master Instructor 2007