Yes. Add a new rule to highlights cells greater than the maximum of 7 digits (9999999.999).

Solved

Posted on 2011-10-07

We have a spread sheet with a column that contains numbers in the format of 0000000.000. 7 numbers dot 3 numbers. some columns erroneously contain excess digits. For example 00000000.000 (8 numbers dot 3 numbers) or 7 numbers dot four. Is there any way to use conditional formatting-Hightlight cell rules to expose which cells contain excessive digits.

Thanks

Lee

Thanks

Lee

7 Comments

For the examples with greater than 4 numbers after the decimal point, instead of using conditional formatting just select the row and format the cells to the format.

This article gives a good run down on the options available when formatting numbers

http://office.microsoft.co

No formatting will help where the number of digits before the decimal point is greater that 7 to use conditional formatting

1/ Select the columns

2/ Select the Home tab

3/ Select Conditional Formatting on the Styles Panel

4/ Select Highlight cells rules

5/ Select more rules

Format Only Cells That Contain

Cell Value

greater than or equal to

100000000

Michael

You can add these formulas into two new empty columns in your spreadsheet:

This formula will return a True if the number of digits to the left of the decimal (for the value in A1) is more than 7 digits:

=LEN(MID(A1,1,FIND(".",A1)

This second formula will return a True if the number of digits to the right of the decimal (for the value in A1) is more than 3:

=LEN(MID(A1,FIND(".",A1)+1

Place these on row 1 and copy down.

You may also be able to use these to apply conditional formatting, but I didn't give that a try.

Let me know if this helps, or if you have any questions on how to use these.

This looks to be the right track. If my column starts at B2 would i modify the formula like this.

=LEN(MID(B2,2,FIND(".",B2)

I tried this and it returns a result of "false" which I guess its supposed to. If I modify

the the cell to exceed the limit, it returns a "value" result. is this correct?

thank you

Details are provided in the attached example.

ConditionalForatting.xlsx

The two formulas are shown in column B and C for example use only. They can be deleted and the conditional formatting will still work.

My first answer did not look close enough to see that you need a upper limit and a decimal count test.

Yes, just replace A1 with whatever your cell is that has the value you wish to test (B2, if it is in the 2nd row, 2nd column). "value", though, means you have something wrong in the formula, so you may want to make certain you don't have any typos there.

If you're able to, feel free to attach a copy of your actual spreadsheet (or an abbreviated version of it), and I can check what you have entered for your formulas. I could also see then if something else unexpected is tripping up the formula.

Take care,

sah18

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

How to pickup the same digit in two column in Excel | 12 | 44 | |

VBA Help TT V.1 | 9 | 28 | |

Updating Pivot Table within VBA | 5 | 28 | |

rank minimum order | 9 | 16 |

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**9** Experts available now in Live!