Excel Formula Question 2

[elfa]

Dear Sir or Madam,

From cell A1 to cell A1000 the cells contain various names of men and ladies in the following format:

Cell A1 Smith, Andrew Mr
Cell A2 Hatcher, Melissa Mrs
Cell A3 Mitchell, Geoff Mr

I need to write a formula so that in column B I have the surname only with no comma.

I also need to write a formula so that in column C I have the first name only with no title (Mr Mrs etc)

Thanks very much for your help it is always appreciated

Elfa

 

[ajrimmer]

Making some assumptions about data consistency...

=LEFT(A1,FIND(",",A1)-1)

=TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)-(LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

 

[SkipVought]

Hi,

Very simply, if ALL your names are EXACTLY that structure, then use Data > Text to columns -- DELIMITED

First using the COMMA, then the SPACE as a delimiter. Your whole list could be parsed in less than 15 seconds!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}