GOAL Check anywhere within a cell to see if one of six letter combinations exists. If it does, display "nonprod", otherwise display "prod".
目标检查单元格中的任何地方，看看是否存在六个字母组合中的一个。如果是，显示“nonprod”，否则显示“prod”。
BACKGROUND I have 1,735 computer names with all sorts of different naming conventions, but they all have one thing in common: the identification of the environment where they operate. The abbreviations of the environments within the server names are "dev", "stg", "qa", "tst", "lt", or "prf". The computer names are in a column called "Host" ($A:$A) and look like this: csap-dev01 (a "dev" server), cssp-qa01 (a "qa" server), cstg-stg-ps-01 (a "stg" server), etc. Sometimes the host names don't fit any of these naming conventions, in which case they are assumed to be "prod" servers.
我有1735个计算机名，有各种不同的命名习惯，但它们都有一个共同点:它们操作的环境的标识。服务器名称中的环境的缩写是“dev”、“stg”、“qa”、“tst”、“lt”或“prf”。计算机名在一个名为“Host”($ a:$ a)的列中，看起来是这样的:csap-dev01(一个“dev”服务器)，cssp-qa01(一个“qa”服务器)，cstg-stg-ps-01(一个“stg”服务器)等等。
MY SOLUTION In a column called "Lifecycle", I created a formula to check if the host name contains any of the Lifecycle values. It if does, then the cell should display "nonprod", otherwise, it should display "prod".
我在一个名为“Lifecycle”的专栏中创建了一个公式，检查主机名是否包含生命周期值。如果是，则单元格应显示“nonprod”，否则应显示“prod”。
=IF(ISERROR(FIND("lt",$A403,5)),IF(ISERROR(FIND("prf",$A403,5)),IF(ISERROR(FIND("tst",$A403,5)),IF(ISERROR(FIND("qa",$A403,5)),IF(ISERROR(FIND("stg",$A403,5)),IF(ISERROR(FIND("dev",$A403,5)),"prod","nonprod"),"nonprod"),"nonprod"),"nonprod"),"nonprod"),"nonprod").
=如果(返回错误(找到(“lt”,A403美元,5)),如果(返回错误(找到(“脉冲”,A403美元,5)),如果(返回错误(找到(“测试”,A403美元,5)),如果(返回错误(找到(“质量保证”,A403美元,5)),如果(返回错误(找到(“stg A403美元,5)),如果(返回错误(找到(“开发”,A403美元,5)),“刺激”,“nonprod”),“nonprod”),“nonprod”),“nonprod”),“nonprod”),“nonprod”)。
ERRORS WITH FIND When I saw that the FIND function will also use an array of values, I shortened the formula to =IF(ISERROR(FIND(Lifecycle,$A403)),"prod","nonprod"), but it always returns #VALUE!. I've just tried a basic FIND like this, =FIND(Lifecycle,$A403), and it only works if "dev" is in the name, otherwise it returns #VALUE!.
当我看到FIND函数也会使用数组的值时，我将这个公式缩短为=IF(ISERROR(FIND(生命周期，$A403))，“prod”，“nonprod”，但它总是返回#值!我刚刚尝试了一个基本的查找，如this， =FIND(生命周期，$A403)，它只在“dev”的名称中起作用，否则它将返回#值!
YOUR HELP I could sure use your help.
你的帮助我肯定会用你的帮助。
1 个解决方案
#1
0
Your question as no ? so I'm not sure what you are after but a shorter version than your formula that works might be:
你的问题是?所以我不确定你想要什么但是比你的公式更短的版本可能是:
=IF(SUM(IF(IFERROR(FIND(NPlist,A403),0),1))>0,"nonprod","prod")
where NPlist is a Defined Name for your server name environment abbreviations.
其中NPlist是服务器名称环境缩写的定义名称。
It is an array formula so requires entry with Ctrl+Shift+Enter.
它是一个数组公式，所以需要使用Ctrl+Shift+Enter。
#1
0
Your question as no ? so I'm not sure what you are after but a shorter version than your formula that works might be:
你的问题是?所以我不确定你想要什么但是比你的公式更短的版本可能是:
=IF(SUM(IF(IFERROR(FIND(NPlist,A403),0),1))>0,"nonprod","prod")
where NPlist is a Defined Name for your server name environment abbreviations.
其中NPlist是服务器名称环境缩写的定义名称。
It is an array formula so requires entry with Ctrl+Shift+Enter.
它是一个数组公式，所以需要使用Ctrl+Shift+Enter。