I am trying to replace all non-zero values in a range in a spreadsheet with
1. I want to tranform my abundance data to presence/absence for use in
another program. Find and replace does not seem to do this. Even if I used
conditional formatting to change the nonzero values to another color, it
still tells me that it can't find anything. I am trying to do this faster
than one column at a time.

To replace all non-zero values in a range with 1 in Microsoft Excel, follow these steps:

1. Select the range of cells that you want to replace the non-zero values with 1.
2. Press Ctrl + H on your keyboard to open the Find and Replace dialog box.
3. In the Find what field, type
   Formula:

   "0" 

   to find all cells that contain a zero.
4. In the Replace with field, type
   Formula:

   "1" 

   to replace all zeros with ones.
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. Make sure that the Search: option is set to By Rows or By Columns, depending on your data layout.
8. Click on the Replace All button to replace all non-zero values with 1 in the selected range.

This should replace all non-zero values with 1 in the selected range. If you have multiple ranges that you want to replace, you can repeat these steps for each range.

Select your cells and run:

Sub unity()
For Each r In Selection
If r.Value = 0 Then
Else
r.Value = 1
End If
Next
End Sub
--
Gary''s Student - gsnu200755


"Amylacc" wrote:

I am trying to replace all non-zero values in a range in a spreadsheet with
1. I want to tranform my abundance data to presence/absence for use in
another program. Find and replace does not seem to do this. Even if I used
conditional formatting to change the nonzero values to another color, it
still tells me that it can't find anything. I am trying to do this faster
than one column at a time.

If your data values are all numeric....Try this:

Select your data range

1) Convert all 0's to the word "zero"
From the Excel Main Menu:
<edit<replace
Find what: 0
Replace with: zero....that's the word "zero"
Click [Options]....Check: Match entire cell contents.
Click [Replace All]

2) Select all of the remaining numbers
Select the data range again
[F5].....a short cut for <edit<go to
Click [Special]
Check: Constants....Check: ONLY Numbers
Click [OK]

3) Replace those numbers with 1's
Type a 1.....Hold down CTRL and press ENTER.

4) Convert the zero's to 0's
Last....select the data range
<edit<replace
Find what: zero
Replace with: 0
Click [Replace All]

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Amylacc" wrote in message
...
I am trying to replace all non-zero values in a range in a spreadsheet with
1. I want to tranform my abundance data to presence/absence for use in
another program. Find and replace does not seem to do this. Even if I
used
conditional formatting to change the nonzero values to another color, it
still tells me that it can't find anything. I am trying to do this
faster
than one column at a time.

What I ended up doing was using the wildcard for each number. Find 1*, 2*,
etc. and then replace with 1. It was pretty quick and faster than doing it
one cell or column at a time.

"Ron Coderre" wrote:

If your data values are all numeric....Try this:

Select your data range

1) Convert all 0's to the word "zero"
From the Excel Main Menu:
<edit<replace
Find what: 0
Replace with: zero....that's the word "zero"
Click [Options]....Check: Match entire cell contents.
Click [Replace All]

2) Select all of the remaining numbers
Select the data range again
[F5].....a short cut for <edit<go to
Click [Special]
Check: Constants....Check: ONLY Numbers
Click [OK]

3) Replace those numbers with 1's
Type a 1.....Hold down CTRL and press ENTER.

4) Convert the zero's to 0's
Last....select the data range
<edit<replace
Find what: zero
Replace with: 0
Click [Replace All]

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Amylacc" wrote in message
...
I am trying to replace all non-zero values in a range in a spreadsheet with
1. I want to tranform my abundance data to presence/absence for use in
another program. Find and replace does not seem to do this. Even if I
used
conditional formatting to change the nonzero values to another color, it
still tells me that it can't find anything. I am trying to do this
faster
than one column at a time.