NEED HELP: How To Select All Values Greater than 0 with Find and Replace

Copper Contributor

I am having trouble using the find and replace tool. I am trying to select all values greater than 0 and replace them all with 1 to create a binary in a single column. I am working with a dataset of about 700 rows, so this should be an all encompassing command for finding all values greater than 0. 

8 Replies

@ecwaters 

Assuming your 700 values are in cells A1:A700. Then enter in cell B1 formula:

=IF(A1>0,1,0)

and copy this formula down to cell B700.

Then copy range B1:B700 and paste only values.

It's up to you if you want to delete range A1:A700.

@ecwaters 

As variant

- enter 1 in any empty cell, Ctrl+C

- apply filter to your range

- Number filter -> Greater than 0

- Ctrl+V

- clean filter

I am trying to do this with the find and replace tool. I know that this is possible because I have done it before but cannot remember the command that I need to type into the find bar to select all values greater than 0.

@ecwaters 

Are sure that was for Ctrl+H (Find & Replace), not for Filter?

 

I guess here Using Excel Find and Replace in the most efficient way - Ablebits.com are all possible options

Did you ever figure this out?
Can you use filter to find and replace as well?

@89789 

I'm not sure how to do that if values are everywhere in the grid, not only in one column

How do you do this in one column, where you change multiple values at the same time, so you have a series of different values in the column, and depending upon the value, there are different replacements? does that make sense?