i need to convert blank spaces into zero values

[Tim]

I am uploading values from an existing data base. Sometimes there is no items
sold during the time so it just gives me a blank area. I need to change that
to a zero so it will calculate correctly. I have tried @if statements but am
getting no results sinve the area is blank to begin with. All i need is a
statement that says if the area is blank result =0, or the number that is
there.

Thanks for any help.

 

[Jacob Skaria]

--Type 0 in a blank cell which is out of your data area. Say (cell T1)
--Copy the cell.
--Keeping the copy select your data area including cells which have figures
and are blank
--Right click>PasteSpecial>check 'Add'>OK

If this post helps click Yes

 

[T. Valko]

It depends on what kind of calculation you're doing but normally an empty
cell evaluates as 0.

If you need the 0s then you can just fill the empty cells with 0.

Let's assume your range is A1:A10 with some empty cells.
Select the range A1:A10
Hit function key F5>Special>Blanks>OK
Type a 0 then, while holding down the CTRL key hit ENTER

All the empty cells in the range will be filled with 0.

 

[Tim]

It works good for regular cells but these cells must have some type of
formatting from the transfer.

 

[Tim]

When i do this it tells me no cells found. but if i go outside the downloaded
area inot the rest of the worksheet it works fine. There must be something
in those blank spaces other than nothing. I dont think its formatting. What
else could be there that i could change.

 

[T. Valko]

Let's assume one of those "empty" cells is A1.

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

 

[Tim]

#name?

Let's assume one of those "empty" cells is A1.

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

 

[T. Valko]

What results do you get?

#name?

Hmmm...

Well, I don't know how you got that for the results. The LEN formula should
return a number from 0 to n. The CODE formula should return either some
variable number or, if the cell really is empty, a #VALUE! error.

 

[Tim]

Thank you so much for your help so far. I think i understand: With the
=code i am getting random numbers anywhere from 32, 48, 50, 51 depending
which space i am putting it in.

When i put in =len i am getting "1".

 

[T. Valko]

OK...

code 32 = a space character
code 48 = the number 0
code 50 = the number 2
code 51 = the number 3

What kind of data is in this range of cells? Is it all supposed to be
numbers?

If it's all supposed to be numbers try this:

Select the range of cells in question
Goto the menu Edit>Replace
Find what: enter a space character by hitting the space bar
Replace with: nothing, leave this empty
Replace All

 

[Tim]

Thank You So very Much. It worked great and you have saved me a ton of work.

 

[T. Valko]

You're welcome. Thanks for the feedback!