Cana forumla extract what I need

[easybpw]

Hello and thanks in advance for the help. Below is a string of characters and I need to extract the last name of the employee. The format will always be the same, there will always be 3 numbers to the left with a space between. I can extract everything to the left but my limited knowledge doesn't allow me to do what I need. In my example below I am looking for "Smith".

Tender type: Staff Charge Employee ID: 99999999 Cost centre: 005 Smith,John

Thanks again!

 

[Eric W]

Try:

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(",",A1),LEN(A1),"")," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[spydey]

Hello and thanks in advance for the help. Below is a string of characters and I need to extract the last name of the employee. The format will always be the same, there will always be 3 numbers to the left with a space between. I can extract everything to the left but my limited knowledge doesn't allow me to do what I need. In my example below I am looking for "Smith".

Tender type: Staff Charge Employee ID: 99999999 Cost centre: 005 Smith,John

Thanks again!

Is there always going to be a comma directly after the last name?

Will there ever be a comma in any other part of the string, other than directly after the last name?

-Spydey


P.S. Looks like Eric W. beat me to it .... hehehe.