complex IF functions

[vmassera]

Hi,

I'm looking to see what function would apply if I want to replicate some text from a column to another one but by modifying slightly its contents.

So a quick example I attached here: I want it so that when it says "sunny INSIDE" on one column, then it will say automatically "sunny OUTSIDE" on the other column at the same row.

Also can it be done with multiple possibilities? i.e. if I use "rainy INSIDE" then it goes "rainy OUTSIDE" etc. and if there is nothing, the cell stays blank.

How do we do this?

Thanks!!

[#2]

@vmassera 

Let's say you have a text value in B2.

Enter the following formula in C2:

=SUBSTITUTE(B2,"INSIDE","OUTSIDE")

This can be filled down if required.

[#3]

@Hans Vogelaar 

Thank you so much for this!

Now can you do it automatically for the whole column?

So as soon as I enter a value (i.e. xyz "INSIDE") on the first column, then it will match the same value on the same row in the other column (xyz "OUTSIDE") ?

[#4]

@vmassera 

Simply fill or copy the formula down as far as you want/need.

[#5]

What about if I want the same formatting (i.e. if the original text is red, make it red in the other cell too?)

[#6]

@vmassera 

That requires VBA.

See the attached version.

The code is in the worksheet module. Right-click the sheet tab and select View Code to see it.

[#7]

Wow thanks! Never delved into macros until now, but thank you for including this code. I'll have to start learning those!