Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# IF ignoring special characters

Posted on 2013-11-29
Medium Priority
155 Views
=IF(LEFT(B2,5)="Slide","",IF(AND(ISNUMBER(B2),B2>0),"",IF(B2="a name","","Action")))

Hi, if B2 contains character  -------

i would like to ignore, leave blank, i would implicity state this but the amount of dashes varies e.g

-------
-----------------

and on some lines are stacked on tope of each other

Thanks
0
Question by:Seamus2626
• 3
• 2

LVL 43

Expert Comment

ID: 39685503
=IF(or(LEFT(B2,5)="Slide",LEFT(B2,5)="-----"),"",IF(AND(ISNUMBER(B2),B2>0),"",IF(B2="a name","","Action")))
0

Author Comment

ID: 39685510
Hi ssaqibh,

that did not ignore

===================

thanks
0

LVL 50

Expert Comment

ID: 39685517
Perhaps try

=IF(OR(LEFT(B2,5)="Slide",COUNTIF(B2,"*--*")),"",IF(AND(ISNUMBER(B2),B2>0),"",IF(B2="a name","","Action")))

regards, barry
0

LVL 43

Expert Comment

ID: 39685522
replace the ----- with =====
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 39685525
Or maybe

=IF(or(LEFT(B2,5)="Slide",LEFT(B2,5)="-----",LEFT(B2,5)="====="),"",IF(AND(ISNUMBER(B2),B2>0),"",IF(B2="a name","","Action")))
0

Author Closing Comment

ID: 39685543
Barry, your formula dramatically increased what i was expecting back. ssaqibh bought back what i was expecting

many thanks
0

## Featured Post

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.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month12 days, 10 hours left to enroll