Excel:用公式中的数字替换字符串吗? [英] Excel: Replace strings with numbers in a formula?

问题描述

我的一列中有颜色字符串，例如红色，紫色等.我想用相应的数字替换这些颜色.红色变成1，紫色变成2，依此类推.

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")

现在，问题在于某些列具有2种或更多种颜色，例如红色紫色"，所以我尝试使用:

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")

这将导致我的列中的值看起来像1red.我添加的每个& SUBSTITUTE都附有该行的颜色.如果我添加了另一种颜色

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")

它将变成1redred.

it would become 1redred.

如何解决此问题?我想用相应的数字替换每个颜色字符串.

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

谢谢！

推荐答案

尝试一下

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

这篇关于Excel:用公式中的数字替换字符串吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！