Assuming your data in A1 and down

In C1:
=MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))=65)* (CODE(MID(A1,ROW($2:$99),1))<90),)+1,99)

ctrl+shift+enter, not just enter


In B1: =SUBSTITUTE(A1,C1,)
Just preess enter



"Miki" wrote:

Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out the way.

Any help will be appreciated..

Thanks in advance..

Miki