SOLVED

Replace function

Iron Contributor

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?

 

anupambit1797_0-1678428045758.png

 

Thanks & Regards

Anupam Shrivastava

 

12 Replies

@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.

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

 

Many Thanks ,

Br,

Anupam

best response confirmed by anupambit1797 (Iron Contributor)
Solution

@anupambit1797 

In A5, enter the formula =IF(ISODD(A1),"Odd","Even")

S2323.png

Fill to the right to L5, then fill down to row 7.

S2324.png

With A5:L7 still selected, copy the range (click Copy on the Home tab of the ribbon or press Ctrl+C).

S2325.png

Select A1, then click the lower half of the Paste button on the Home tab of the ribbon and select Values.

S2326.png

You can now clear A5:L7.

S2327.png

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

Thanks ,
Br,
Anupam

@anupambit1797 

Would a VBA macro be OK?

@Hans Vogelaar @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):

mtarler_0-1678462258650.png

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:

mtarler_1-1678462430001.png

 

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

@mtarler 

Nice idea!

Sorry , I am not much comfortable with VBA :(

@anupambit1797 

Try @mtarler's suggestion.

Thanks @Hans Vogelaar , @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..

 

anupambit1797_0-1679086091330.png

 

Can you please elaborate more.

 

Thanks ,

Br,

Anupam

 

 

@anupambit1797 

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

S2350.png

S2349.png

Thank you very much @hans :)
1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

@anupambit1797 

In A5, enter the formula =IF(ISODD(A1),"Odd","Even")

S2323.png

Fill to the right to L5, then fill down to row 7.

S2324.png

With A5:L7 still selected, copy the range (click Copy on the Home tab of the ribbon or press Ctrl+C).

S2325.png

Select A1, then click the lower half of the Paste button on the Home tab of the ribbon and select Values.

S2326.png

You can now clear A5:L7.

S2327.png

View solution in original post