Replace function

[anupambit1797]

Dear Experts,

                   I have a situation , where I want to replace the integers within the red-box with a Text = Even/Odd, so 0 shall be replaced by Even, 1 shall be replaced by Odd, any quick method to achieve this?

Thanks & Regards

Anupam Shrivastava

[#2]

@anupambit1797 

You could use formulas in a different range of the form

=IF(ISODD(cell, "Odd", "Even"))

Then copy the range with the formulas and paste it as values over the range in the red box.

[#3]

@HansVogelaar, Thanks Hans, I attach a sheet "Test.xlsx" could you please share on how to do this.

Many Thanks ,

Br,

Anupam

[#4 · Accepted solution]

@anupambit1797 

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.

[#5]

Thanks a lot Hans, may I also ask for one alternate solution as well, just in case if any possibility..

Thanks ,
Br,
Anupam

[#6]

@anupambit1797 

Would a VBA macro be OK?

[#7]

@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

[#8]

@mtarler 

Nice idea!

[#9]

Sorry , I am not much comfortable with VBA 😞

[#10]

@anupambit1797 

Try @mtarler's suggestion.

[#11]

Thanks @HansVogelaar , @mtarler , I tried to use the suggestion , I could reach till here, but not sure how to edit the one encircled with Red, with Odd and Even text..

Can you please elaborate more.

Thanks ,

Br,

Anupam

[#12]

@anupambit1797 

Instead of changing the fill colorpattern, set the number format in the conditional formatting rules.

[#13]

Thank you very much @hans 🙂