SOLVED

Iron Contributor

# Replace function

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

12 Replies

# Re: Replace function

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.

# Re: Replace function

@HansVogelaar, 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

# Re: 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.

# Re: Replace function

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

Thanks ,
Br,
Anupam

# Re: Replace function

Would a VBA macro be OK?

# Re: Replace function

@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

Nice idea!

# Re: Replace function

Sorry , I am not much comfortable with VBA 😞

# Re: Replace function

Try @mtarler's suggestion.

# Re: Replace function

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

# Re: Replace function

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

# Re: Replace function

Thank you very much @hans 🙂
1 best response

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

# Re: 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.