Format cells with 0 as the tenth digit

  • Thread starter Thread starter PointerMan
  • Start date Start date
I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).
 
assumed cell A1 has some value, if the tenth digit is 10 then cell
should be colored.

go to format | conditional formatting | condition 1: formula is: =FIND
("0",A1,10)=10 | choose color | ok
 
Select the cells then Format

CF>Formula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP
 
I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).
Click to expand...

Use a formula for the conditional formatting:

=AND(LEN(TRUNC(A1))=10,RIGHT(TRUNC(A1),1)="0")

--ron
 
That didn't work. It kept giving me an error that I couldn't root cause.
One other thing - I'm looking for the tenth digit equalling zero, not 10.
 
Hi,

If the tenth digit is "10" then the tenth digit is "1" and the eleventh
digit in "0".

An additional point - 10th digit from the left counting or not counting
decimal points?


To conditionally format your cell(s): Assume the cell is C10 in the
following

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=-MID(C10,10,1)=0
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=-MID(C10,10,1)=0
5. Click the Format button and choose a format.
6. Click OK twice
 
Gord,

That did it. Thanks! Now is there a way to delete all of the cells that I
just highlighted?
 
Shane

I found that =0 won't work whereas ="0" will.

I guess because we are using a text function?


Gord
 
If that is the only CF you have on the sheet hit F5>Special>Conditional
Formats>OK

Hit the delete key.


Gord
 
Yeah.....................my booboo.

Select all cells then Edit>Find>Format>Format>Pattern(pick your CF color)
and OK

Find All.

In the "found" dialog box CTRL + a to select all then Edit>Delete.


Gord
 
Thanks again Gord, but it doesn't want to recognize the format color. It
says that it cannot find the cells with that format.
 
Back
Top