how do I change all numbers to "1"

[belharris]

we have a massive excel spreadsheet downloaded from surveymonkey where values
in the columns range from 1 to 40 in hundreds of columns. I know how to
find/replace 1 number at a time like change all 2's to 1's. Is there a quick
way to change all numbers 2, 3, 4, ect to 1 with one swoop?

 

[JE McGimpsey]

One way:

1) Select all the values
2) Copy them
3) Without changing the selection, choose Edit/Paste Special,
choosing the Values and DIvide radio buttons. Click OK.

 

[belbeason]

I LOVE this forum, my husband is crying at the simplicity of this solution
after spending 4 years doing it one number at a time for a spreadsheet that
has hundreds of columns and numbers
THANK YOU!!!

 

[Sheeloo]

Yes, this forum is great. I kind of feel bad for years of wasted effort.

What I am unable to understand is why would you download something and then
convert everything to ones? How does that help?

By the another way is to press F5, Click on Special, Choose Constants
then press 1 and press CTRL-ENTER

 

[belbeason]

What if I want to find and fill blank cells with 0?

 

[belbeason]

What if I want to fill in all the blank cells with 0?

 

[JE McGimpsey]

One way: choose Subtract rather than Divide.

What if I want to fill in all the blank cells with 0?