software

Hi there,

I have a column in an excel spreadsheet that is populated with one of two
things:

1. Normal text: i.e. words, numbers, addresses, etc...

or

2. A single 0 (zero)

What I want to do is execute a Find/Replace or similar command that will
eliminate the text in the cell if it is a single zero _without_ deleting any
other zeroes in any of the other cells.

Doing a simple find/replace does get rid of the zeroes in the quot;zero cellsquot;
but also gets rid of zeroes in other cells (so -- like an address that has a
number 12007 becomes 127).

In smaller spreadsheets, I can simply hand-delete the quot;zero cellsquot; but now
I'm starting to get spreadsheets that are over a thousand lines long and
hand-deleting is not time effective.

Thanks very much in advance!!

Hi!

Try this:

Select the range of cells in question
Goto Editgt;Replace
Find what: 0
Replace with: nothing, leave this blank
Click the Options button
Check Match entire cell contents
Replace All

Biff

quot;Treyquot; gt; wrote in message
...
gt;
gt; Hi there,
gt;
gt; I have a column in an excel spreadsheet that is populated with one of two
gt; things:
gt;
gt; 1. Normal text: i.e. words, numbers, addresses, etc...
gt;
gt; or
gt;
gt; 2. A single 0 (zero)
gt;
gt; What I want to do is execute a Find/Replace or similar command that will
gt; eliminate the text in the cell if it is a single zero _without_ deleting
gt; any
gt; other zeroes in any of the other cells.
gt;
gt; Doing a simple find/replace does get rid of the zeroes in the quot;zero cellsquot;
gt; but also gets rid of zeroes in other cells (so -- like an address that has
gt; a
gt; number 12007 becomes 127).
gt;
gt; In smaller spreadsheets, I can simply hand-delete the quot;zero cellsquot; but now
gt; I'm starting to get spreadsheets that are over a thousand lines long and
gt; hand-deleting is not time effective.
gt;
gt; Thanks very much in advance!!
Edit|Replace

If you don't see the all the options, click that Options button.

There's a spot where you can specify quot;Match entire cell contentsquot;

Make sure you select just the range you want to fix first.

Trey wrote:
gt;
gt; Hi there,
gt;
gt; I have a column in an excel spreadsheet that is populated with one of two
gt; things:
gt;
gt; 1. Normal text: i.e. words, numbers, addresses, etc...
gt;
gt; or
gt;
gt; 2. A single 0 (zero)
gt;
gt; What I want to do is execute a Find/Replace or similar command that will
gt; eliminate the text in the cell if it is a single zero _without_ deleting any
gt; other zeroes in any of the other cells.
gt;
gt; Doing a simple find/replace does get rid of the zeroes in the quot;zero cellsquot;
gt; but also gets rid of zeroes in other cells (so -- like an address that has a
gt; number 12007 becomes 127).
gt;
gt; In smaller spreadsheets, I can simply hand-delete the quot;zero cellsquot; but now
gt; I'm starting to get spreadsheets that are over a thousand lines long and
gt; hand-deleting is not time effective.
gt;
gt; Thanks very much in advance!!

--

Dave Peterson

Hi,

See whether the following approach helps.

Let's suppose that the data are in A2:A1001. Create a helper column (say
B2:B1001), by entering the following formula in B2 and autofilling down to
B1001.

=IF(LEN(TRIM(A2))gt;1,A2,IF(ISERROR(A2*1),A2,IF(A2*1 lt;gt;0,A2,quot;quot;)))

Select B2:B1001 --gt; quot;Editquot; --gt; quot;Copyquot; --gt; quot;Editquot; --gt; quot;Paste Specialquot; --gt;
quot;Valuesquot; --gt; quot;OKquot; (This is to unlink Column B from Column A, so that the
latter can be deleted if desired)

Regards,
B. R. Ramachandranquot;Treyquot; wrote:

gt;
gt; Hi there,
gt;
gt; I have a column in an excel spreadsheet that is populated with one of two
gt; things:
gt;
gt; 1. Normal text: i.e. words, numbers, addresses, etc...
gt;
gt; or
gt;
gt; 2. A single 0 (zero)
gt;
gt; What I want to do is execute a Find/Replace or similar command that will
gt; eliminate the text in the cell if it is a single zero _without_ deleting any
gt; other zeroes in any of the other cells.
gt;
gt; Doing a simple find/replace does get rid of the zeroes in the quot;zero cellsquot;
gt; but also gets rid of zeroes in other cells (so -- like an address that has a
gt; number 12007 becomes 127).
gt;
gt; In smaller spreadsheets, I can simply hand-delete the quot;zero cellsquot; but now
gt; I'm starting to get spreadsheets that are over a thousand lines long and
gt; hand-deleting is not time effective.
gt;
gt; Thanks very much in advance!!