If there's not an ampersand, I want cell to be empty - it gives me #VALUE! instead

[johnmerlino]

Hey all,

This is pretty much complete. The only issue I'm having is that if there's not ampersand, then just make the cell empty. Rather, it gives me VALUE error:

=LEFT(A1,IF(ISERROR(FIND("&",A1,1)),"",FIND("&",A1,1)-1))

It doesn't like "" for some reason. Any other way to make the cell empty?

For example, this is what column A1 may have:
AVERNA,ROBERT C

Because no ampersand, it should just return empty space in whatever column the calculation is performed in.

Thanks for response.

 

[jlatendre]

This should work hopefully,

=IF(ISERR(LEFT(A1,IF(ISERR(FIND("&",A1,1))," ",FIND("&",A1,1)-1)))," ",LEFT(A1,IF(ISERR(FIND("&",A1,1))," ",FIND("&",A1,1)-1)))

Jesse

 

[shg]

Perhaps

=IF(ISERROR(FIND("&",A1)), "", LEFT(A1, FIND("&", A1) - 1))

 

[chandrashekar]

Hi,

Try this

=IF(ISERROR(LEFT(A1,FIND("&",A1,1))),"",LEFT(A1,FIND("&",A1,1)))

 

[johnmerlino]

Thanks for responses. They all look good. I'll test them out.