Conditional Formatting Pbm

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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-

 

[Guest]

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

 

[Guest]

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??

 

[Guest]

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

 

[Guest]

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.