Solved

Convert to an IF statement

Posted on 2011-09-08
11
232 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 250 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

707 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