How to Handle FIND when character not present

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

I am attempting to show only the First Name of a Full Name field,
however, I run into issues when only the first name has been entered
and there is therefore no space. When that happens, I get #VALUE!.
This is how I have tried to tackle that, but it didn't work:

=IF(H36="","",IF((LEFT(H36,FIND("
",H36)))="#VALUE!","",LEFT(H36,FIND(" ",H36))))

Any advice?

magmike
 
Hi magmike,

Am Thu, 26 Jan 2012 05:08:13 -0800 (PST) schrieb magmike:
I am attempting to show only the First Name of a Full Name field,
however, I run into issues when only the first name has been entered
and there is therefore no space. When that happens, I get #VALUE!.
Click to expand...

try:
=IF(OR(H36="",ISERROR(FIND(" ",H36))),"",LEFT(H36,FIND(" ",H36)-1))


Regards
Claus Busch
 
Hi magmike,

or use Data => Text to Columns to seperate first name and last name


Regards
Claus Busch
 
Just add a <space> to the end of the string.  Find will always return the location of the first space.

=LEFT(H36,FIND(" ",H36&" ")-1)
Click to expand...

Great - both great ideas - Thanks!

As for the Data to columns - I am creating a template that others will
use, and I'd rather have the formula do the work then have to teach
others about the Data to Columns feature.

Thanks to your help I was able to write this one that pulls the last
name out of the contact name and leaves the title out (I don't need it
at all):

=IF(OR(H7="",ISERROR(FIND("
",H7)),ISERROR(FIND(",",H7))),"",MID(H7,FIND(" ",H7)+1,(FIND(",",H7))-
FIND(" ",H7)-1))

Thanks!

magmike
 
Great - both great ideas - Thanks!

As for the Data to columns - I am creating a template that others will
use, and I'd rather have the formula do the work then have to teach
others about the Data to Columns feature.

Thanks to your help I was able to write this one that pulls the last
name out of the contact name and leaves the title out (I don't need it
at all):

=IF(OR(H7="",ISERROR(FIND("
",H7)),ISERROR(FIND(",",H7))),"",MID(H7,FIND(" ",H7)+1,(FIND(",",H7))-
FIND(" ",H7)-1))

Thanks!

magmike- Hide quoted text -

- Show quoted text -
Click to expand...

Oops. It is almost working - but now I am back to getting an error
when there is just a first name. I thought the following would address
that, but apparently I've done something wrong! Any ideas?

=IF(H20="","",IF(OR(H20="",ISERROR(FIND("
",H20)),ISERROR(FIND(",",H20))),RIGHT(H20,(LEN(H20))-(FIND("
",H20))),MID(H20,FIND(" ",H20)+1,(FIND(",",H20))-FIND(" ",H20)-1)))

magmike
 
Hi magmike,

Am Thu, 26 Jan 2012 09:38:03 -0800 (PST) schrieb magmike:

Oops. It is almost working - but now I am back to getting an error
when there is just a first name. I thought the following would address
that, but apparently I've done something wrong! Any ideas?
Click to expand...

try:
=IF(OR(H20="",ISERROR(FIND(",",H20))),"",TRIM(MID(H20,FIND("#",SUBSTITUTE(H20," ","#",LEN(H20)-LEN(SUBSTITUTE(H20," ","")))),99)))


Regards
Claus Busch
 
Back
Top