How do I find all nonzero values in an excel spreadsheet?

[alarimer]

And then replace them with 1? I have searched the web and not been able to come up with a way to do this. What I want is to replace the nonzero values with 1 to get a presence/absence data set. It is a huge file (4146 rows by 41 columns) so doing them one at a time is tedious at best.

But I can't seem to put any value in the find box. I tried things like >1 but that does not work. I am at a loss. The Microsoft website is unhelpful.

[Fredda Weinberg]

1. VBA script for automation

you'll see a formula ... it starts w/ the = sign

then you'll want to replace

here's instructions

http://techonthenet.com/excel/formulas/replace.php

[alarimer]

2. Thanks but that isn't quite the right thing

There is a Find and Replace function that should work but I can't figure out how to make it do what I want. I even used conditional formatting to change the color and then tried to find it by that but it keeps telling me it can't find anything.

I don't see an option to specify all values >0.

[TAZller]

3. Array formula...

Select an adjacent area of exactly the same dimensions then type the formula ENTERING USING CTRL+SHIFT+ENTER..... if($a$1:$$>0,1,0). Then copy....paste special.....values over the source numbers....

[Fredda Weinberg]

4. try this

http://www.eggheadcafe.com/software/aspnet/30309403/greater-than-findreplace.aspx

[Zorro]

5. Probably 2 steps

1. Replace all cells with a zero to a blank.

2. Then replace all non-zero cells with a 1. Use the wildcard "*" to represent what you're replacing with a 1.

[alarimer]

6. I need the zeros in there though

I don't know how to find nonzero values in the first place. The wildcard does not seem to work. I put in the asterisk and it tells me nothing was found.

[Zorro]

7. Variation on the theme

1. Duplicate the workheet.

2. On worksheet version 1, change all cells that are zero to blanks. (Enter 0 in the replace box, leave the second box blank, and replace all.)

3. On worksheet version 1, change all cells with anything to 1. (Enter * in the replace box, enter 1 in the second box, and replace all.)

4. Select and copy version 1 worksheet.

4. Paste Special worksheet 1 into worksheet 2, but check the box "skip blanks". This should do it, if I understand the problem you're trying to solve.

[alarimer]

8. What I ended up doing is using a wildcard *

First I did find and replace for 1*, then 2*, all the way through 9. It seemed to work and wasn't too time consuming.

Thanks, though.