Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing specific characters
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing specific characters
Hi!
Try this: Select the range of cells in question Goto EditReplace Find what: 0 Replace with: nothing, leave this blank Click the Options button Check Match entire cell contents Replace All Biff "Trey" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing specific characters
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. Trey wrote: 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!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing specific characters
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 "Trey" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find cells that contain specific characters | Excel Worksheet Functions | |||
trim a string by specific number of characters | Excel Discussion (Misc queries) | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) | |||
Replacing specific characters with spaces | Excel Worksheet Functions | |||
Pulling out specific characters | Excel Discussion (Misc queries) |