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!
Try this
=SUBSTITUTE(SUBSTITUTE(E3,"Purple","2"),"Red","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.
