Excel: Replace strings with numbers in a formula? -

Excel: Replace strings with numbers in a formula? -

i have color strings in 1 of columns, red, violet , on. want replace colors corresponding numbers. reddish becomes 1, violet becomes 2 , on.

thats not hard, used substitute, this:

substitute(e3;"red";"1")

now problem ist columns have 2 or more colors, "red purple", tried using:

substitute(e3;"red";"1")&substitute(e3;"purple";"2")

that results in value in column looks 1red, there color row attached each &substitute add. if added color, that

substitute(e3;"red";"1")&substitute(e3;"purple";"2")&substitute(e3;"green";"3")

it become 1redred.

how can solve issue? want replace each color string corresponding number.

thanks!

try this

=substitute(substitute(e3,"purple","2"),"red","1")

excel excel-2010