[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Convert to an IF statement

Posted on 2011-09-08
Medium Priority
235 Views
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
0
Question by:jpldpd
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 33

Accepted Solution

Rob Henson earned 1000 total points
ID: 36502193
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

Author Comment

ID: 36502275
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

LVL 10

Expert Comment

ID: 36502277
=IF(B2="","",PROPER(LEFT(B2,FIND(".",B2)-1)) )
0

LVL 10

Expert Comment

ID: 36502285
OR
=IF(ISBLANK(B2),"", PROPER(LEFT(B2,FIND(".",B2)-1)))
0

LVL 10

Expert Comment

ID: 36502295
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

LVL 85

Expert Comment

ID: 36502317
FYI, Rob's formula works fine for me in 2007.
0

Author Comment

ID: 36502327
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

LVL 85

Expert Comment

ID: 36502357
NOT FOR POINTS.

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

Author Comment

ID: 36502387
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

Author Closing Comment

ID: 36502396
This is exactly what I needed. Thanks!
0

LVL 33

Expert Comment

ID: 36503104

Rob H
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month12 days, 19 hours left to enroll