How to make Conditional Formatting Ignore '+' & '-' values

[liamfrancis2013]

Hi,

I'm attempting to use conditional formatting to compare values, however i want '+' and '-' to be ignored.

For example if two cells were the same my sheet will highlight the one of the cells yellow. However if one cell contained '3' & the other contained '3+/3-' conditional formatting recognizes
'3+/3-' as a greater value than '3' so this rule would not be applied.

So i want my conditional formatting rule to ignore + and - symbols next to numbers.

I've attached an example sheet.

Thanks,
Liam

[BadlySpelledBuoy]

By using a formula approach to conditional formatting you could use the below:

Please Login or Register  to view this content.

See attached.

You could use a formula based around =LEFT() to make it a bit shorter but if the figures could be greater than 1 digit then that could be less straightforward than the above.

BSB

[sandy666]

something like that: =OR(A2>=B2,A2<B2) ?

or =A2=--MID(B2,1,1)

[Phuocam]

Formula for CF:

=SUBSTITUTE(SUBSTITUTE(B2,"-",""),"+","")-A2=0

[liamfrancis2013]

The solutions brilliant although i didn't mention that column A can also contain '-' & '+' values.

So, for example if column A contains 3+ and column B contains 3 the conditional formatting wont be applied

[sandy666]

try =--MID(A2,1,1)=--MID(B2,1,1)

of course you can use LEFT() but I like MID() function

[liamfrancis2013]

Thanks works a charm. Giving everyone rep. Appreciate it.

[sandy666]

You are welcome

If the basic problem is solved
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks