How to Handle FIND when character not present

[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

 

[Claus Busch]

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!.

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


Regards
Claus Busch

 

[Claus Busch]

Hi magmike,

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


Regards
Claus Busch

 

[Ron Rosenfeld]

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

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)

 

[magmike]

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)

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

 

[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 -

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

 

[Claus Busch]

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?

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


Regards
Claus Busch

 

[Ron Rosenfeld]

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

For a more focused solution, please provide a comprehensive set of examples of the type of data with which you are dealing, and what you expect for output.

As you note, providing limited data may not provide solutions that will work on different data.