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

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

"PointerMan" wrote:

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

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


On Feb 8, 12:06*am, PointerMan
wrote:
I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).

"PointerMan" wrote:
How do I format cells that have a zero for the tenth digit?

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

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.


"muddan madhu" wrote:

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


On Feb 8, 12:06 am, PointerMan
wrote:
I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).

"PointerMan" wrote:
How do I format cells that have a zero for the tenth digit?

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
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"muddan madhu" wrote:

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


On Feb 8, 12:06 am, PointerMan
wrote:
I meant to say that I want to do a conditional format on the selected cells
(highlight them some color).

"PointerMan" wrote:
How do I format cells that have a zero for the tenth digit?

Gord,

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



"Gord Dibben" wrote:

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

Shane

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

I guess because we are using a text function?


Gord


On Sat, 7 Feb 2009 11:46:00 -0800, Shane Devenshire
wrote:

4. In the second box enter the formula:
=-MID(C10,10,1)=0
5. Click the Format button

If that is the only CF you have on the sheet hit F5SpecialConditional
FormatsOK

Hit the delete key.


Gord

On Sat, 7 Feb 2009 11:56:01 -0800, PointerMan
wrote:

Gord,

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



"Gord Dibben" wrote:

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Shane

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

I guess because we are using a text function?


Gord


On Sat, 7 Feb 2009 11:46:00 -0800, Shane Devenshire
wrote:

4. In the second box enter the formula:
=-MID(C10,10,1)=0
5. Click the Format button

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


"Gord Dibben" wrote:

If that is the only CF you have on the sheet hit F5SpecialConditional
FormatsOK

Hit the delete key.


Gord

On Sat, 7 Feb 2009 11:56:01 -0800, PointerMan
wrote:

Gord,

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



"Gord Dibben" wrote:

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

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

Select all cells then EditFindFormatFormatPattern(pick your CF color)
and OK

Find All.

In the "found" dialog box CTRL + a to select all then EditDelete.


Gord


On Sun, 8 Feb 2009 12:25:00 -0800, PointerMan
wrote:

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


"Gord Dibben" wrote:

If that is the only CF you have on the sheet hit F5SpecialConditional
FormatsOK

Hit the delete key.


Gord

On Sat, 7 Feb 2009 11:56:01 -0800, PointerMan
wrote:

Gord,

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



"Gord Dibben" wrote:

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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

Most certainly did miss that.

Thanks, Gord

On Sat, 7 Feb 2009 17:58:45 -0500, "Rick Rothstein"
wrote:

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

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

"Gord Dibben" wrote:

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

Select all cells then EditFindFormatFormatPattern(pick your CF color)
and OK

Find All.

In the "found" dialog box CTRL + a to select all then EditDelete.


Gord


On Sun, 8 Feb 2009 12:25:00 -0800, PointerMan
wrote:

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


"Gord Dibben" wrote:

If that is the only CF you have on the sheet hit F5SpecialConditional
FormatsOK

Hit the delete key.


Gord

On Sat, 7 Feb 2009 11:56:01 -0800, PointerMan
wrote:

Gord,

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



"Gord Dibben" wrote:

Select the cells then Format

CFFormula is: =MID(A1,10,1)="0"

Or if 0 is always last digit

=RIGHT(A1)="0"


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:02 -0800, PointerMan
wrote:

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

"PointerMan" wrote:

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