New Contributor

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

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

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

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.

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

As variant

- enter 1 in any empty cell, Ctrl+C

- apply filter to your range

- Number filter -> Greater than 0

- Ctrl+V

- clean filter

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

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.

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

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

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

Did you ever figure this out?

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

Can you use filter to find and replace as well?

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

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

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

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?