Conditional Formatting Pbm
 

Hi Folks
I have a financial spreadsheet that was created from a webpage by importing
the data. Data seems to have imported OK and cells are correctly formatted at
"Currency". But when I apply a CF of Cell value is less than 0, and the cell
contains a minus number, it absolutely ignores it. In fact, if I apply a CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

Thanks much Bob

1. how do you tell if there is mixed text and numbers?

2. how would I reformat for all numbers if there is text imbedded? I tried
changing the cell category and also copying and paste special - values only,
but that didnt seem to do anything either.

3. I tried your suggestion and changed the CF to "Formula Is =--J8<0" but
the cell with a negative value in it still does not format properly. Any
other suggestions?

Regards-

"Bob Phillips" wrote:

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

One method to convert text to numbers is by multiplying by 1

1. Enter 1 in any unused cell.
2. Select the cell and copy (Control-C)
3. Highlight the cells which are to be converted to numbers.
4. Click on Edit/PasteSpecial and select multiply.

Alok

"RjS, CISSP" wrote:

Thanks much Bob

1. how do you tell if there is mixed text and numbers?

2. how would I reformat for all numbers if there is text imbedded? I tried
changing the cell category and also copying and paste special - values only,
but that didnt seem to do anything either.

3. I tried your suggestion and changed the CF to "Formula Is =--J8<0" but
the cell with a negative value in it still does not format properly. Any
other suggestions?

Regards-

"Bob Phillips" wrote:

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

Thanks Alok

However, I have tried that, and all the other suggestions in the help files,
and the cells with numbers in them are still left justified and when I try to
use them in a formula it seems as if they are being treated as text - not
numbers. Is there anything I can do to force these cells into beging treated
as numbers??


"Alok" wrote:

One method to convert text to numbers is by multiplying by 1

1. Enter 1 in any unused cell.
2. Select the cell and copy (Control-C)
3. Highlight the cells which are to be converted to numbers.
4. Click on Edit/PasteSpecial and select multiply.

Alok

"RjS, CISSP" wrote:

Thanks much Bob

1. how do you tell if there is mixed text and numbers?

2. how would I reformat for all numbers if there is text imbedded? I tried
changing the cell category and also copying and paste special - values only,
but that didnt seem to do anything either.

3. I tried your suggestion and changed the CF to "Formula Is =--J8<0" but
the cell with a negative value in it still does not format properly. Any
other suggestions?

Regards-

"Bob Phillips" wrote:

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

Hi,

A simple search of the problem on Google groups showed that the following
may be a cure. This points to a macro which you run. It removes some special
characters that come along from Web pages. After running this macro you may
still need to run the solution that I gave you.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Alok

"RjS, CISSP" wrote:

Thanks Alok

However, I have tried that, and all the other suggestions in the help files,
and the cells with numbers in them are still left justified and when I try to
use them in a formula it seems as if they are being treated as text - not
numbers. Is there anything I can do to force these cells into beging treated
as numbers??


"Alok" wrote:

One method to convert text to numbers is by multiplying by 1

1. Enter 1 in any unused cell.
2. Select the cell and copy (Control-C)
3. Highlight the cells which are to be converted to numbers.
4. Click on Edit/PasteSpecial and select multiply.

Alok

"RjS, CISSP" wrote:

Thanks much Bob

1. how do you tell if there is mixed text and numbers?

2. how would I reformat for all numbers if there is text imbedded? I tried
changing the cell category and also copying and paste special - values only,
but that didnt seem to do anything either.

3. I tried your suggestion and changed the CF to "Formula Is =--J8<0" but
the cell with a negative value in it still does not format properly. Any
other suggestions?

Regards-

"Bob Phillips" wrote:

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.

Thanks much Alok... that link you published is a great resource, both for
this problem and for others.

The problem did turn out to be a trailing nonprint character, probably &nbsp
dragged in from html. Another good clue was when a number cell was clicked on
and the cursor was placed into the formula bar just after the number, and you
tried to append some characters; you would see the nonprinting character as a
mysterious space.

I happened to fix this problem by dumping the whole sheet into a csv file,
opening it with notepad then going to the find/replace dialog I copied the
nonprinting character by highlighting the "space" and then replaced it with
nothing. That fixed the problem so I could then import data into excel and
have excel recognize the numbers as numbers.

Thanks again for the great tip on that link resource. Have a great weekend.


"Alok" wrote:

Hi,

A simple search of the problem on Google groups showed that the following
may be a cure. This points to a macro which you run. It removes some special
characters that come along from Web pages. After running this macro you may
still need to run the solution that I gave you.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Alok

"RjS, CISSP" wrote:

Thanks Alok

However, I have tried that, and all the other suggestions in the help files,
and the cells with numbers in them are still left justified and when I try to
use them in a formula it seems as if they are being treated as text - not
numbers. Is there anything I can do to force these cells into beging treated
as numbers??


"Alok" wrote:

One method to convert text to numbers is by multiplying by 1

1. Enter 1 in any unused cell.
2. Select the cell and copy (Control-C)
3. Highlight the cells which are to be converted to numbers.
4. Click on Edit/PasteSpecial and select multiply.

Alok

"RjS, CISSP" wrote:

Thanks much Bob

1. how do you tell if there is mixed text and numbers?

2. how would I reformat for all numbers if there is text imbedded? I tried
changing the cell category and also copying and paste special - values only,
but that didnt seem to do anything either.

3. I tried your suggestion and changed the CF to "Formula Is =--J8<0" but
the cell with a negative value in it still does not format properly. Any
other suggestions?

Regards-

"Bob Phillips" wrote:

You probably have text in the data.

You can either reformat, or change your CF to a formula, like =--A2<0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RjS, CISSP" wrote in message
...
Hi Folks
I have a financial spreadsheet that was created from a webpage by
importing
the data. Data seems to have imported OK and cells are correctly formatted
at
"Currency". But when I apply a CF of Cell value is less than 0, and the
cell
contains a minus number, it absolutely ignores it. In fact, if I apply a
CF
of Cell Value is Greater Than 0, and the cell contains a MINUS number, the
CF
returns true and applies the CF formatting.

Any Suggestions?

Thanks in advance.