how to delete a character from the character

Dear All,

I got this dilemma on how I can delete the character ",et.al" in my text characters.

For Example, the data is "Mary Jane, et.al." but I only wanted the data "Mary Jane"

Attached is my excel file.

Thanks


Rey

Here..

Didn't check your formula.. Just added on top of it..

Formula:

=TRIM(LEFT(SUBSTITUTE("             "&(IF(ISERROR(VLOOKUP($S$20&"A",INDIRECT("'"&$S$20&"'!AG:AY"),5,FALSE)),"",VLOOKUP($S$20&"A",INDIRECT("'"&$S$20&"'!AG:AY"),5,FALSE))), ",", REPT(" ",255)),255))

Hi
you can't delete it because it is a formula linked to another cell, and "et al" is in that cell, but this tweak to your formula should fix it

="             "&SUBSTITUTE((IF(ISERROR(VLOOKUP($S$20&"A",INDIRECT("'"&$S$20&"'!AG:AY"),5,FALSE)),"",VLOOKUP($S$20&"A",INDIRECT("'"&$S$20&"'!AG:AY"),5,FALSE))),", et.al.","")

oops - zbor beat me to it!