Convert to an IF statement

Hello,

I am using the following formula to extract text from a string containing a period. =PROPER(LEFT(B2,FIND(".",B2)-1)) However, if there is no period I get the typical error code #VALUE!.

I would like to use an if statement that basically says, IF the string contains a period then run formula =PROPER(LEFT(B2,FIND(".",B2)-1)), otherwise leave the cell blank.

Thanks,

John
JohnAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
For xl07 and later:

=IFERROR(PROPER(LEFT(B2,FIND(".",B2)-1)),"")

For xl03 and prior

=IF(ISERROR(PROPER(LEFT(B2,FIND(".",B2)-1))),"",PROPER(LEFT(B2,FIND(".",B2)-1)))

Thanks
Rob H
0
 
JohnAuthor Commented:
Hello,

Thank you for your reply.  I wanted to mention though, that the formula you suggested for Excel 07 gave me blank results regardless but the solution for 03 worked. I'm using 2007.

Thanks,

John
0
 
ukerandiCommented:
=IF(B2="","",PROPER(LEFT(B2,FIND(".",B2)-1)) )
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ukerandiCommented:
OR
=IF(ISBLANK(B2),"", PROPER(LEFT(B2,FIND(".",B2)-1)))
0
 
ukerandiCommented:
if you used ISERROR function any error will ignore,so other than blank fields errors you can't see if you used ISERROR function
0
 
Rory ArchibaldCommented:
FYI, Rob's formula works fine for me in 2007.
0
 
JohnAuthor Commented:
Hello,

I'm sorry if I confused the issue. I am not looking at a blank cell, I am looking for a period within a string. Please see the attached file. I hope this helps to be more clear.

Thanks,

John
Name-Parser-Sample-2.xlsx
0
 
Rory ArchibaldCommented:
NOT FOR POINTS.

It appears you entered Rob's formula incorrectly (referring to the row below). See attached.
Name-Parser-Sample-2.xlsx
0
 
JohnAuthor Commented:
Hello Rory,

You're correct. I did not realize I messed up with the cell reference. I apologize for any confusion.

Rob, thank you!

John
0
 
JohnAuthor Commented:
This is exactly what I needed. Thanks!
0
 
Rob HensonFinance AnalystCommented:
Glad to be of help.

Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.