?
Solved

Convert to an IF statement

Posted on 2011-09-08
11
Medium Priority
?
233 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
[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
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 33

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 33

Expert Comment

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

Rob H
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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