Replacing specific characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 "zero cells"
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 "zero cells" 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 Edit>Replace
Find what: 0
Replace with: nothing, leave this blank
Click the Options button
Check Match entire cell contents
Replace All

Biff
 
Edit|Replace

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

There's a spot where you can specify "Match entire cell contents"

Make sure you select just the range you want to fix first.
 
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))>1,A2,IF(ISERROR(A2*1),A2,IF(A2*1<>0,A2,"")))

Select B2:B1001 --> "Edit" --> "Copy" --> "Edit" --> "Paste Special" -->
"Values" --> "OK" (This is to unlink Column B from Column A, so that the
latter can be deleted if desired)

Regards,
B. R. Ramachandran
 
Back
Top