copy contents of one column to another column BUT change just a few words

hello,

in column A, I have:
red
blue
green
black
white
yellow

i would like column B to copy these, however, "blue" and "black" should be changed to the word "dark"
whilst "white" and "yellow" is changed to "bright"

red and green should be as they are.

so column B will be:
red
dark
green
dark
bright
bright


thank you

try this fella

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"Black","Dark"),"Blue","Dark"),"Yellow","Bright"),"White","Bright")

thanks.

i'll be using this for 18 different colours.
is it possible to write the formula in a way to simplify it and make it a lot shorter.
so for example to list "black","blue","brown","navy","purple" but only mention "dark" once rather than "black","dark"),"blue","dark"),"brown","dark").........

I can write something, but you could always do find and replace?

i cant replace because i need the initial column as it is for another calculation.
plus the data set is continually growing so i would prefer a formula that forever does the job rather than having to find and replace in the future everytime new data is added.

please write it out for me
thanks

I would say the best way to do this if its often is make a table elsewhere on the sheet listing
cells like
red
yellow
blue
black
etc

then next to them in col B what you want them to be for example: bright, dark

Then in your main sheet do a vlookup along the lines of
=vlookup(a1,$A$1:B$10,2,0)

Cheers

untitled.JPG


like this

but column A is not necessarily as simple looking like that.

you might have to go hundred of rows down to come across a colour for the first time.
is it not just possible for a formula similar to your first one, but a bit shorter and simple.

if not, then i can make do with the initial formula.
thanks

How many colours do you have?
Can you get a screen shot for me? That would help loads?

Be good if we could get this sorted for you

there are 50 colours in total in the column.
18 need to have their words changed.
9 colours changed to "dark"
9 colours changed to "light"

thats is.

sorry, i cant give you a screenshot.
the data im working with is not about colours - its highly sensitive data.

thank you for your assistance.

Yeah its a tough one as the data is vast,
I would say the Substitute formula is the way forward.
If i come up with anything else i will let you know

cheers mate.
appreciate it