Solved

Convert to an IF statement

Posted on 2011-09-08
11
231 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Technology Partners: 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

737 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