Excel: Replace strings with numbers in a formula?

Question

I have color strings in one of my columns, like red, purple and so on. I want to replace those colors with corresponding numbers. Red becomes 1, purple becomes 2 and so on.

Thats not so hard, I used substitute, like this:

SUBSTITUTE(E3;"red";"1")

Now the problem ist that some columns have 2 or more colors, like "red purple", so I tried using:

SUBSTITUTE(E3;"red";"1")&SUBSTITUTE(E3;"purple";"2")

That results in a value in my column that looks like 1red, There is the color from that row attached for each &SUBSTITUTE I add. If I added another color, like that

SUBSTITUTE(E3;"red";"1")&SUBSTITUTE(E3;"purple";"2")&SUBSTITUTE(E3;"green";"3")

it would become 1redred.

How can I solve this issue? I want to replace each color string with its corresponding number.

Thanks!

Answer 1

Please consider the following more compact solutions (assuming tested cell is A2):

• Using MATCH: if you need to return sequenced numbers like 1, 2, 3 ... - this formula will do the job:

  =IFERROR(MATCH(A2,{"Red","Green","Blue"},0),"UNKNOWN COLOR")
  

You may add a multiplier / constant to the returned value as well. Order in sequence of strings equals the number returned.

• Using VLOOKUP: if you need some defined set of returned values - define them in 2-dim array constant:

  =IFERROR(VLOOKUP(A2,{"Red",10;"Green",20;"Blue",30},2,0),"UNKNOWN COLOR")
  

For this example 10, 20 and 30 will be returned.

Both formulas include error handling for unspecified colors.

Sample file is shared: https://www.dropbox.com/s/77aj1vl6c5gek5c/ColorsLookup.xlsx

P.S. I'm not sure about correct array dimension delimiters, since my local settings use different ones, but in sample file formulas work fine.

Answer 2

Try this

=SUBSTITUTE(SUBSTITUTE(E3,"Purple","2"),"Red","1")