Forum Discussion
Replace function
In A5, enter the formula =IF(ISODD(A1),"Odd","Even")
Fill to the right to L5, then fill down to row 7.
With A5:L7 still selected, copy the range (click Copy on the Home tab of the ribbon or press Ctrl+C).
Select A1, then click the lower half of the Paste button on the Home tab of the ribbon and select Values.
You can now clear A5:L7.
HansVogelaar, Thanks Hans, I attach a sheet "Test.xlsx" could you please share on how to do this.
Many Thanks ,
Br,
Anupam
In A5, enter the formula =IF(ISODD(A1),"Odd","Even")
Fill to the right to L5, then fill down to row 7.
With A5:L7 still selected, copy the range (click Copy on the Home tab of the ribbon or press Ctrl+C).
Select A1, then click the lower half of the Paste button on the Home tab of the ribbon and select Values.
You can now clear A5:L7.
- Thanks a lot Hans, may I also ask for one alternate solution as well, just in case if any possibility..
Thanks ,
Br,
AnupamWould a VBA macro be OK?
HansVogelaar anupambit1797 Here is a fun alternative. Use 2 conditional formatting rules (or you could apply the custom number format to the range and use only 1 rule):
Basically you create a custom rule that says =ISODD(A1) then you format the true responses with a custom format "Odd"
you can repeat a 2nd rule for Even or you could just apply a custom format for the whole range to be shown as "Even" and let the custom formatting rule change the odd values.
here is the 'custom cell format' screen:
NOTE: this does NOT replace/change the values in those cells! This only changes the view. How you SEE the cells. Another words the VALUE in cell A1 is still 0 it is just showing the user "Even". Depending on what you need this can be good (i.e. retains the original values) or bad (you cannot use a formula to lookup "Even" in that range nor could you filter for "Even" and if you are trying to 'hide' the original values, the user can still click on the cells and see those values in the formula bar).
EDIT: i attached a sample file
