Forum Discussion
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
- Did you ever figure this out?
As variant
- enter 1 in any empty cell, Ctrl+C
- apply filter to your range
- Number filter -> Greater than 0
- Ctrl+V
- clean filter
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.
- 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.
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
