Format cells with 0 as the tenth digit

[PointerMan]

How do I format cells that have a zero for the tenth digit?

 

[PointerMan]

I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).

 

[muddan madhu]

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

 

[Gord Dibben]

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

 

[Ron Rosenfeld]

I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).

Use a formula for the conditional formatting:

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

--ron

 

[PointerMan]

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.

 

[Shane Devenshire]

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

 

[PointerMan]

Gord,

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

 

[Gord Dibben]

Shane

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

I guess because we are using a text function?


Gord

 

[Gord Dibben]

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

Hit the delete key.


Gord

 

[Rick Rothstein]

Did you miss the minus sign in front of the MID function?

 

[PointerMan]

When I do this it highlights all columns of data, not just the highlighted
cells.

 

[Gord Dibben]

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

 

[Gord Dibben]

Most certainly did miss that.

Thanks, Gord

 

[PointerMan]

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