☰

检查单元格中的第一个字母是否在 Excel 中提供的范围内

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
检查单元格中的第一个字母是否在 Excel 中提供的范围内

我在 A 列中有一个公司列表。我希望另一列检查这些公司的首字母是否在 {"a","b","c","d","e","f","g","h","A","B","C","D","E","F","G","H"} 范围内。我想 LEFT 会提供这个功能，只要我能让它检查多个字符。

为了便于理解，我将函数嵌套在多个 IF 中。

=IF(SEARCH("**",D112),IF(LEFT(A112,1)={"a","b","c","d","e","f","g","h","A","B","C","D","E","F","G","H"}," 供应商 AH",IF(LEFT(A112,1)={"i","j","k","l","m","n","o","p","I","J","K","L","M","N","O","P"}," 供应商 JP",IF(LEFT(A112,1)={"q","r","s","t","u","v","w","x","y","z","Q","R","S","T","U","V","W","X","Y","Z"}," 供应商 QZ",""))),"")

答案1

首先在某处创建一个命名范围（我称之为SearchList），其中包含值“a”到“h”。大写字母会被忽略，因此无需同时包含小写和大写字母。

那么测试公式很简单：

=MATCH(LEFT(A2,1),SearchList,0)

答案2

尝试这个 -

="Supplier "&IF(AND(CODE(UPPER(LEFT(A5,1)))>=CODE("A"),CODE(UPPER(LEFT(A5,1)))<=CODE("H")),"A-H",IF(AND(CODE(UPPER(LEFT(A5,1)))>=CODE("J"),CODE(UPPER(LEFT(A5,1)))<=CODE("P")),"J-P",IF(AND(CODE(UPPER(LEFT(A5,1)))>=CODE("Q"),CODE(UPPER(LEFT(A5,1)))<=CODE("Z")),"Q-Z","Not Alphabetical"))))

答案3

如果列表在 A 列，则将其输入到 B1 中并将其向下拖动：

=IF(LEFT(A1,1)<"I","供应商 AH",IF(LEFT(A1,1)<"Q","供应商 IP","供应商 QZ"))

相关内容