find and replace nonzero values with 1

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

Guest

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.
 
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
 
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)
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top