VBA:删除所有特殊字符的功能 [英] VBA: Function to remove all special characters

问题描述

我已经做了一些寻找解决方案的尝试，但是我找不到可以直接使用的东西.因此，通过尝试编辑供我自己使用的其他人的代码，我遇到了一个问题.

I've done some searching for a solution to this and I've not been able to find something I can use directly. Therefore, by trying to edit someone else's code for my own use, I've come to an issue.

我正在尝试使用 RemoveSpecialChars("$ C $ 1")这一行，但出现了 Object Required 错误.

I'm trying to use the line RemoveSpecialChars("$C$1") but I get an Object required error.

功能如下:

Function RemoveSpecialChars(ByVal mfr As Range)

    Const splChars As String = "!@#$%^&()/"
    Dim ch As Characters

    For Each ch In splChars
    mfr.Replace What:=ch, Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    Next ch

End Function

这是一个简单的问题，但"$ C $ 1"是否不被视为范围?

This is a simple question, but is "$C$1" not considered a range?

感谢您的帮助.

如果我使用 Function RemoveSpecialChars(mfr As Range)(没有 ByVal )，则出现类型不匹配的情况错误.

If I use Function RemoveSpecialChars(mfr As Range) (without ByVal), I then get a Type mismatch error.

这进一步使我相信​​ $ C $ 1"不被视为范围.

This further leads me to believe that "$C$1" is not considered a range.

@ sous2817

@sous2817

这是我设置字符串 mfr 与您的函数一起使用的方式.

This is how I set up the string mfr to be used with your function.

Range("C1").FormulaR1C1 = mfr

这就是我尝试应用它的方式.

This is how I attempt to apply it.

Range("C1").FormulaR1C1 = RemoveSpecialChars(C1)

推荐答案

不要传递"$".这对我有用:

Don't pass the "$". This worked for me:

=RemoveSpecialChars(C1)

现在这带来了其他问题，因为代码只能执行集合和数组，而不能迭代字符串，因此无法执行您想要的操作.

Now this opens up other issues in that the code son't do what you want it to do as you can only iterate over collections and arrays, not strings.

尝试一下(我敢肯定可以清理一下，但是希望可以使您朝正确的方向前进):

Try this (which I'm sure can be cleaned up, but hopefully will get you going the right direction):

Function RemoveSpecialChars(ByVal mfr As Range)

    Dim splChars As String
    Dim ch As Variant
    Dim splCharArray() As String

    splChars = "! @ # $ % ^ & () /"

    splCharArray = Split(splChars, " ")


    For Each ch In splCharArray
    mfr.Replace What:=ch, Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    Next ch
    RemoveSpecialChars = mfr

End Function

如果您是通过子级调用的，则应该限定范围引用，这是一种方法:

IF you're calling this from a sub, you should qualify your range reference, here is one way:

Sub test()

    Dim test As String

    test = RemoveSpecialChars(Sheet1.Range("C1"))

    Debug.Print test

End Sub

由于问题而更新.这是填充公式的另一种方法:

due to question update. Here is ANOTHER way to populate the formula:

Sub test2()
    Sheet1.Range("D1").Formula = "=RemoveSpecialChars(C1)"
End Sub

确定，最后一次编辑.如果要保留该字符串并创建一个删除了特殊字符的字符串，则应采取略有不同的方法.这是一种方法:

Ok, last edit. If you want to preserve the string and create one with special characters removed, you should take a slightly different approach. Here is one way:

Function RemoveSpecialChars(mfr As Range)

    Dim splChars As String
    Dim ch As Variant
    Dim splCharArray() As String
    Dim newString As String

    newString = mfr.Value

    splChars = "! @ # $ % ^ & () /"

    splCharArray = Split(splChars, " ")


    For Each ch In splCharArray
        newString = Replace(newString, ch, "")
    Next ch

    RemoveSpecialChars = newString

End Function

这篇关于VBA:删除所有特殊字符的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！