regex - 从 Google 表格中的单元格中删除 " 和 =+

Question

我有像这样的细胞

"Apple"
=+Organe +is +good
"Mango"

我想删除所有字符 ，即=和"+

试过=SUBSTITUTE(C3,"+" ,"",1)但没用

我正在使用 Google 表格，但无法使用 Excel（在 MAC 中）

标签： regexgoogle-sheetsgoogle-sheets-formulaarray-formulassubstitution

Answer 1

如果您知道应该删除哪些字符和多少个字符，则N 嵌套替换将起作用。只需确保输入单元格不是错误：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"="," "),"+"," "),"""","")

如果您需要排除除字母字符 A 到 Z 以及 0 到 9 之间的数字之外的任何内容，VBA + RegEx 就会发挥作用：

Public Function RemoveNonAlphabetChars(inputString As String) As String

    Dim regEx           As Object
    Dim inputMatches    As Object
    Dim regExString     As String

    Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        .Global = True
        .Pattern = "[^a-zA-Z0-9]"
        .ignoreCase = True
        Set inputMatches = .Execute(inputString)

        If regEx.test(inputString) Then
            RemoveNonAlphabetChars = .Replace(inputString, vbNullString)
        Else
            RemoveNonAlphabetChars = inputString
        End If
    End With

End Function