Replace numbers with zero

[bigband1]

Hello All
I have a column of numbers in col AG.
Is there a macro or formula which will replace all numbers above 9 with zero.Some cells in col G,are blank
Data is around 50000 rows.
Many Thanks for help given.

[berlan]

Try,

Please Login or Register  to view this content.

[Leith Ross]

Hello bigband1,

Are all the number in column "AG" ? I ask because you also mention "Some cells in col G,are blank".

Are you referring to whole numbers or the digits in the number when you test for a 9?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kalak]

Perhaps, as a very much amateur at this stuff, I'm missing something, but the rather juvenile and easy (for me) to follow, looping code below seems to solve the OP problem in about 1 second for 1 million rows, and in very negligible time for only 50,000.

Please Login or Register  to view this content.

[bigband1]

Thanks for the reply Leith
My error,"Some cells in G",should read AG
Regards

[jindon]

Please Login or Register  to view this content.

[berlan]

Jindon, nice and concise to use evaluate, but sometimes this particular code seems unreliable when it works and when it doesn't work.

[jindon]

Jindon, nice and concise to use evaluate, but sometimes this particular code seems unreliable when it works and when it doesn't work.

What do you mean?