仅保留字母字符的公式

Question

我需要在Excel中清洗一个姓名列，以消除所有非字母字符，包括句号、逗号、空格、连字符和撇号。

例如：将 O'Malley-Smith, Tom, Jr. 改为 OMALLEYSMITHTOMJR。

客户要求这是一个Excel函数，否则我可以轻松地用类似于 replaceAll("[^a-zA-Z]", "").toUpperCase() 的快速Java程序解决它。除了一堆SUBSTITUTE函数之外，我似乎找不到任何看起来像现成函数的东西——而且每个单元格只能使用一个。

如果需要，我对开发自定义宏并不是非常熟练。

- dwwilson66

---

请注意：仅包含列表中的字符。参见从范围中删除特殊字符。 - T.M.

Answer 1

虽然使用多个SUBSTITUTE函数会很混乱，但在单个单元格中也是可能的，例如：

Indeed a mess of SUBSTITUTEs but within a single cell is possible, eg:

=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"'",""),".",""),"-",""))   

当然，可能需要“扩展”以涵盖其他非字母字符。

- pnuts

---

巧妙的公式化方法。 - brettdj

谢谢你的好意，但公式并不是我的强项。我认为Barry没有任何对手，他就是最好的，没得说。 - brettdj

非常好的解决方案，可以去除有限的唯一非数字字符串。当我需要将诸如“14D”、“8M”、“9hr”和“23min”之类的键转换为数字值时，这个方法对我很有效，只需使用“D”、“M”、“hr”和“min”作为替代即可。 - e_i_pi

优秀的解决方案，可以消除少量字符。 - YosiN

Answer 2

我以前有过类似的需求，后来发现了一个很好用的东西。

按下Alt+F11打开Visual Basic编辑器。插入一个新的模块并粘贴以下代码。

Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(UCase(Rng), n, 1))
            Case 48 To 57, 65 To 90
                strTemp = strTemp & Mid(UCase(Rng), n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

现在的CleanCode是一个新的函数，你可以把它当做公式使用。

所以，在想要操作字符串的单元格旁边，只需复制 =CleanCode(yourcell)

- aurezio

Answer 3

如果您想采用VBA方法，那么您不能使用用户定义函数(UDF)来改变您输入函数的单元格的值，但是您可以使用一个简单的宏，并利用微软的VBScript正则表达式引擎。

Sub SO()

Dim searchRange     As Excel.Range
Dim cell            As Variant
Dim RegEx           As Object

Set RegEx = CreateObject("VBScript.RegExp")

With RegEx
    .Pattern = "[^a-zA-Z]"
    .Global = True
    .MultiLine = True
End With

Set searchRange = ActiveSheet.Range("A1:D5") '// Change as required

    For Each cell In searchRange.Cells
        If RegEx.test(cell) Then cell.Value = RegEx.Replace(cell.Value, vbNullString)
        cell.Value = UCase(cell.Value)
    Next cell

Set searchRange = Nothing
Set RegEx = Nothing

End Sub

- SierraOscar

---

正则表达式是最有效的VBA方法。虽然最好在变量数组中使用它，而不是在范围内使用。 - brettdj

Answer 4

这可以在Excel 2016及以上版本中用单个公式完成。

虽然pnuts的解决方案列出了要去除的显式字符，但此解决方案明确列出了有效字符。

假设您的脏数据在A列中。 假设您想要干净的数据在B列中。

在单元格B1中使用下面的最后一个公式。要将公式输入到单元格B1中，请执行以下操作：

• 点击单元格B1
• 点击公式栏
• 粘贴公式
• 按下CTRL+Shift+Enter<--重要步骤

复制单元格B1并将其向下粘贴到您需要的位置。

首先，这里有一个简短的示例来解释正在发生的事情：

=TEXTJOIN("",TRUE,

IFs(
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "t", "t",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "e", "e",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "s", "s",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "T", "T",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "E", "E",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "S", "S",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "2", "2",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = " ", " ",
    true, ""
  )

)

在这种情况下，我已经指定了以下字符作为有效字符：t、e、s、T、E、S、2和空格字符。

显然，您需要将公式扩展到完整的字母表中，并分别列出每个大写和小写字符。同样，如果要保留数字，则需要将其扩展出来。请注意，数字被编码为字符串。

工作原理：

简而言之，我们将源字符串拆分成一个单独字符的数组，然后对于每个字符，检查它是否在我们的一组有效字符中，并在其中加入结果，否则，如果不是，则用空字符串替换它。

“IFS”函数按成对出现的参数进行处理。如果第一个参数评估为true，则返回第二个参数。如果不是，则继续使用下一对参数——这就是为什么你在每行上看到字母重复两次的原因。IFS函数中的最后一对值是“true”和空字符串集。这意味着如果我们到达有效值集的末尾（即未匹配有效值），则返回空字符串。

更多关于工作原理的背景知识：

这是在ExcelJet提供的解决方案的变体。在该解决方案中，使用TEXTJOIN函数（用于连接数组的值）与INDIRECT函数（将字符串拆分为数组）以及数学运算符（加号）结合使用，强制计算在具有数值的每个字符之间的计算。字符串中的数字字符将返回数值，而其他字符将返回错误。该解决方案使用ISERR函数来检查错误，以决定是否在最终输出中包含给定的字符。那里还有一篇类似的文章是为了排除数字并保留字母。

我想要解决的问题是编码人员决定哪些值是有效的，哪些不是。我尝试使用VLOOKUP和INDEX函数与INDIRECT函数，但它们只适用于字符串中的第一个字符。诀窍在于，并非所有函数都会对INDIRECT的输出产生作用，以便评估数组中的每个元素（即字符串中的每个字符）。秘密是ExcelJet使用了数学运算符。如果您查看Microsoft的完整函数参考，IFS被归类为“逻辑”函数。我怀疑可以使用逻辑函数以这种方式与INDIRECT一起使用。

（注意：我也尝试使用AND和OR的各种组合。但是INDIRECT会评估字符串中的所有字符。例如，使用CODE函数获取每个字符的ASCII值并断言所有字符必须具有65-90（大写）或97-122（小写字母）之间的值，只有在字符串中所有字符都是大写字母或全部是小写字母时才有效，而不是如果有混合字母。）

我不知道这种解决方案的性能如何与使用SUBSTITUTE的先前建议相比。如果您只想剥离出几个字符，请使用SUBSTITUTE解决方案。如果要明确指定要保留的有效字符（这是最初的问题），则使用此方法。

最后，这里是你需要的精确答案，包括将其转换为大写字母，这是你在问题中没有提到但在示例中显示的。（对于其他不想进行大写转换的人，请从此示例中删除“UPPER”的实例，然后将字母表以小写形式添加到列表中，并确保将“true”/空字符串对留作列表中的最后一个条目。）

=TEXTJOIN("",TRUE,
IFs(
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "A", "A",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "B", "B",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "C", "C",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "D", "D",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "E", "E",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "F", "F",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "G", "G",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "H", "H",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "I", "I",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "J", "J",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "K", "K",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "L", "L",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "M", "M",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "N", "N",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "O", "O",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "P", "P",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Q", "Q",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "R", "R",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "S", "S",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "T", "T",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "U", "U",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "V", "V",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "W", "W",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "X", "X",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Y", "Y",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Z", "Z",
    true, ""
)
)

原问题是“消除所有非字母字符” - 这个答案可以通过公式实现，无需使用 VBA。

- youcantryreachingme

Answer 5

进一步参考上面youcantryreachingme的答案，并结合https://exceljet.net/formula/strip-numeric-characters-from-cell中给出的建议，使用SEQUENCE和LET函数（在较新版的Excel中可用），然后进行进一步实验，我已经将该函数简化为...

=LET(character,MID(a1,SEQUENCE(LEN(a1)),1),TEXTJOIN("",TRUE,
 IFS(
     character=" "," ",
     CODE(UPPER(character))>90,"",
     CODE(UPPER(character))<65,"",
     TRUE,character)
 ))

由于IFS似乎只执行第一个匹配的语句，因此在声明范围之前放置任何要包含的例外情况（>90，<65）非常重要。使用“AND”语句来组合范围限制似乎更直观，但由于上面youcantryreachingme所述的原因，这种方式不起作用。

- Phil

Answer 6

另一个VBA解决方案

Sub RemoveCrap()
    Dim varRange As Range
    Dim varWorkRange As Range

    Set varWorkRange = Range("A1:A10")

    For Each varRange In varWorkRange
        varVal = ""
        For i = 1 To Len(varRange.Value)
            varTemp = Mid(varRange.Value, i, 1)
            If Not (varTemp Like "[a-z]" Or varTemp Like "[A-Z]") Then
                varStr = ""
            Else
                varStr = UCase(varTemp)
            End If
            varVal = varVal & varStr
        Next i
        varRange.Value = varVal
    Next
End Sub

- Pankaj Jaju

---

@user3415869 - 这个概念是可行的，但代码不行，因为Google Sheets使用Google Apps Script而MS Excel使用VBA。 - Pankaj Jaju

Answer 7

由于我刚加入并没有足够的声望，在此无法直接回复。我非常喜欢这个解决方案，并发现它非常高效、简短，符合我的要求——清理文本字段，将过滤后的字符集输出到文件名和Web编码输出需要的列中。

虽然没有直接回答问题，但我觉得这个页面是我找到的最接近我所需解决方案的地方，所以我想给其他人更多的背景信息，并扩展的解决方案，以便更通用的字符选择——如果有人感兴趣的话。

第7行与允许的字符号或数字系列相关。使用Excel函数CODE()来识别字符代码和CHAR()来发布字符代码。

我的标准（如下所示）是过滤除45（破折号）、48至57（数字系列）、65至90（字母数字大写系列）、97至122（字母数字小写系列）之外的所有内容。同时，不需要大写字母意味着可以删除UCase()。

'based off aurezio's solution
Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(Rng, n, 1))
            Case 45, 48 To 57, 65 To 90, 97 To 122
                strTemp = strTemp & Mid(Rng, n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

我希望最终将其改进为动态的，并允许输入所需的过滤器参数。

例如：ClearCode(Range,"45", "48-57", "65-90", "97-122")

- dsdrk

Answer 8

在一个简单的条件集中，我的偏好比上面的Phil更基本，因为它直接进行测试，而不是列出IFS，这在布局方面有自己的问题。为了清晰起见，我将使用LET，尽管我认为在提问时它还不可用，因为它可以通过简单的替换来删除：

=LET(Character,  MID(A1,SEQUENCE(LEN(A1)),1),  CharCode,  CODE(Character),

      TEXTJOIN("",TRUE,  IF( (CharCode>=65) * (CharCode<=90) + ((CharCode>=97) * (CharCode<=122)), UPPER(Character), "" )  )
      )

基本上，最内层的测试是中间的“AND”，首先测试字符是否在大写字母集合中，第二个测试是否在小写字母集合中，这两个集合都是可接受的。为每个数组或TRUE/FALSE生成一个数组，由于有乘法，Excel会将文字TRUE/FALSE转换为1/0。 （如果只有一个数组而没有必要的乘法，那么就不会发生这种情况，因此需要“*1”来强制转换。）

然后将这两个数组相加，保留了单个数组中的所有成功结果。因此，如果字符通过任一测试集，则作为1（TRUE）进入最终数组中。IF使用该数组来保留字符并将其提交给UPPER，或者根本不保留它，并将“”放入输出数组中。两个问题：

1. UPPER可以用于简化事情，因为唯一感兴趣的结果是字母字符。如果可以接受更广泛的字符集，则可能不适合。但是对于大多数可能由海报使用的字符集中的字符，它将简单地返回字符。返回值将是文本，因此数字不会是数字，但由于所需的输出是字符串，因此无关紧要。

2. 需要在输出数组中放置什么以使TEXTJOIN正常工作，以便只需要该函数。如果无法做到这一点，则无法避免更多的复杂性。但是，它愿意在创建字符串时忽略某种类型的输入，即空白。因此，如果输出对于不可接受的字符具有空白，可以告诉它忽略它们，并按照特定要求删除它们。这使“”成为自然选择，但如果希望或愿意在公式之后使用不同的字符和路径，则当然可以。只是不是最简单，最不复杂的方法。

然后，TEXTJOIN仅使用无分隔符组合成功的字符，如所需。

公式的最基本级别在设置LET的变量中。使用MID将输入字符串拆分为单个字符，使用SEQUENCE依次查看每个字符。 SEQUENCE可能在发布时不可用，但是在MID函数中有“老派”的方法来执行相同的操作。因此，对于任何具有当前版本2022及以上版本的人，完全可以剪切和粘贴。

其他方法肯定可以包括所有已经给出的答案，以及仔细设置 IFS（可能是所有非VBA解决方案中最灵活的），以及 SWITCH 方法。如果一个人关注更多，特别是比只有256个字符更多的字符，那么应该记住 Unicode 系统中到处都有数字。出于任何其他原因，一个人可能会得到一个非常分散的列表。在某个点之后，公式可能会变成噩梦般的存在。但是UDF仍然非常简单。

- Jeorje