?
Solved

Convert to an IF statement

Posted on 2011-09-08
11
Medium Priority
?
237 Views
Last Modified: 2012-05-12
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
Comment
Question by:jpldpd
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Accepted Solution

by:
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

by:jpldpd
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

by:ukerandi
ID: 36502277
=IF(B2="","",PROPER(LEFT(B2,FIND(".",B2)-1)) )
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Expert Comment

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

Expert Comment

by:ukerandi
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

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

Author Comment

by:jpldpd
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

by:Rory Archibald
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

by:jpldpd
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

by:jpldpd
ID: 36502396
This is exactly what I needed. Thanks!
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 36503104
Glad to be of help.

Rob H
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question