Hi, I am having a column(A) that h 3

[Deadline]

Hi,
I am having a column(A) that has blanks and numbers among its rows.

My objective is to replace all the blanks with 2 and all the numbers with 1.

Circular reference wasn't permitted and hence, I inserted another column(B) and pasted the following formula in all its rows.

IF(ISNUMBER(A2),1,2)

But all the rows in the new column contain 2, unless I go to (A) where the number is present, and then unless I press F2, the result in column B for the corresponding row remains 1.


Can you help me out of situation ?


Thank you...
RR

 

[mymou]

This often happens in Excel and is very annoying: Numbers appear as Text.

Havent had that problem for a while - but think that using =value(A2) - should do the trick.

Could also try ISBLANK(A2) and see what happens


Stew

 

[dsi]

Have you formatted column A as a number?

 

[Deadline]

Thank you very much both of you...


DSI>> I think I did, But I don't remember clearly. Let me check..

MyMou > Thank you .. I am going to try your strategy too..

Thank you...
RR

 

[Store300]

You can also try = IF(Len(A2)<1,2,1) Store300

Store300@ftnetwork.com

 

[Deadline]

Thank you store.. As I had to rush that particular XL2000 file, I followed the following strategy:

I just saved the file as a csv (comma separated values) file, thereby elminating all the formulas in that sheet. And then I copied and pasted it into the original worksheet. Thank you...
RR