Format cells with 0 as the tenth digit
 

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

Format cells with 0 as 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?

Format cells with 0 as 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?

Format cells with 0 as 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?

Format cells with 0 as 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

Format cells with 0 as the tenth digit
 

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?

Format cells with 0 as 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?

Format cells with 0 as 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?

Format cells with 0 as 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

Format cells with 0 as the tenth digit
 

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?

Format cells with 0 as 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

Format cells with 0 as the tenth digit
 

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?

Format cells with 0 as 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?

Format cells with 0 as 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?

Format cells with 0 as the tenth digit
 

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?