i need to convert blank spaces into zero values
 

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.

Answer: i need to convert blank spaces into zero values
 

Here's how you can convert blank spaces into zero values in Excel:

1. Select the range of cells that you want to convert.
2. Press Ctrl+H to open the Find and Replace dialog box.
3. In the Find what field, type a single space character (" ").
4. In the Replace with field, type
   Formula:

   ---

   "0" 


   ---

   (without the quotes).
5. Click on the Options button to expand the dialog box.
6. Make sure that the Within: option is set to Sheet or Workbook, depending on your needs.
7. Check the Match entire cell contents box to ensure that only cells with a single space character are replaced.
8. Click on the Replace All button to replace all blank spaces with zeros.

That's it! Now all the blank spaces in your selected range will be replaced with zeros. This should allow you to perform calculations correctly even when there are no items sold during a certain time period. Let me know if you have any other questions!

i need to convert blank spaces into zero values
 

--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 clickPasteSpecialcheck 'Add'OK

If this post helps click Yes
---------------
Jacob Skaria


"Tim" wrote:

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.

i need to convert blank spaces into zero values
 

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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


"Jacob Skaria" wrote:

--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 clickPasteSpecialcheck 'Add'OK

If this post helps click Yes
---------------
Jacob Skaria


"Tim" wrote:

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.

i need to convert blank spaces into zero values
 

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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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.


--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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 EditReplace
Find what: enter a space character by hitting the space bar
Replace with: nothing, leave this empty
Replace All

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

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

"T. Valko" wrote:

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 EditReplace
Find what: enter a space character by hitting the space bar
Replace with: nothing, leave this empty
Replace All

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.

i need to convert blank spaces into zero values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
Thank You So very Much. It worked great and you have saved me a ton of
work.

"T. Valko" wrote:

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 EditReplace
Find what: enter a space character by hitting the space bar
Replace with: nothing, leave this empty
Replace All

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
#name?

"T. Valko" wrote:

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

Try these formulas:

=LEN(A1)

=CODE(A1)

What results do you get?

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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" wrote:

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 F5SpecialBlanksOK
Type a 0 then, while holding down the CTRL key hit ENTER

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

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
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.