vba 如何删除字符？

How do I remove the characters?

提问by niko

How do I remove special characters and alphabets in a string ?

如何删除字符串中的特殊字符和字母？

 qwert1234*90)!  ' this might be my cell value

I have to convert it to

我必须将其转换为

 123490  ' I mean I have to remove everything but keep only the numbers in string

but it should allow spaces !

但它应该允许空格！

 qwe123 4567*. 90  ' String with spaces
 123 4567 90     ' output should be

I found the vbaReplace- but writing a replace for each character makes my code big. Well let me tell you clearly without hiding anything from you:

我找到了vbaReplace- 但是为每个字符编写一个替换会使我的代码变大。好吧，让我清楚地告诉你，不要对你隐瞒任何事情：

1. input: qwe123 4567*. 90' String with spaces cells(1,"A").value
2. My idea to do these next: 123 4567 90' remove characters first keeping white spaces
3. final output in A1:A3

   123
   4567
   90

1. 输入：qwe123 4567*. 90' 带有空格的字符串单元格(1,"A").value
2. 我接下来要做的事情是： 123 4567 90' 删除字符首先保留空格
3. 最终输出 A1:A3

   123
   4567
   90

(for every space it should insert row and fill that)（对于每个空间，它应该插入行并填充它）

Could you tell me how do remove all characters except numbers and spaces in string?

你能告诉我如何删除字符串中除数字和空格之外的所有字符吗？

Thanks In advance

提前致谢

回答by JMax

You need to use a regular expression.

您需要使用正则表达式。

See this example:

看这个例子：

Option Explicit

Sub Test()
    Const strTest As String = "qwerty123 456 uiops"
    MsgBox RE6(strTest)
End Sub

Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "([0-9]| )+"   
    End With

    Set REMatches = RE.Execute(strData)
    RE6 = REMatches(0)

End Function

Explanation:
Pattern = "([0-9]| )+"will match any 0 or more group (+) containing a number ([0-9]) or (|) a space ().

说明：
Pattern = "([0-9]| )+"将匹配任何 0 个或多个+包含数字 ( [0-9]) 或 ( |) 空格 ( ) 的组( )。

Some more infoon the regexp:

关于正则表达式的更多信息：

• a thread on ozgrid
• a very good reference about regexp

• ozgrid 上的一个线程
• 关于正则表达式的非常好的参考

回答by Alex K.

Non-re alternative;

不可替代；

Public Function fmt(sValue As String) As String
    Dim i As Long
    For i = 1 To Len(sValue) '//loop each char
        Select Case Mid$(sValue, i, 1) '//examine current char
            Case "0" To "9", " " '//permitted chars
               '//ok
            Case Else
               Mid$(sValue, i, 1) = "!" '//overwrite char in-place with "!"
        End Select
    Next
    fmt = Replace$(sValue, "!", "") '//strip invalids & return
End Function

For:

为了：

?fmt("qwe123 4567*. 90") 
123 4567 90

回答by Aziz

Those two funny codes will do both of your whishes..

这两个有趣的代码将满足您的两个愿望..

Sub MySplitter(strInput As String)
    Row = 10  ' Start row
    Col = "A" ' Column Letter
    Range(Col & Row) = ""   ' Clean the start cell
    For i = 1 To Len(strInput)  ' Do with each Character in input string...
        c = Mid(strInput, i, 1) ' Get actual char
        If IsNumeric(c) Then Range(Col & Row) = Range(Col & Row) & c ' If numeric then append to actual cell
        If (c = " ") And (Range(Col & Row) <> "") Then 'If space and actual row is not empty then...
            Row = Row + 1           ' Jump to next row
            Range(Col & Row) = ""   ' Clean the new cell
        End If
    Next
End Sub

Function KeepNumbersAndSpaces(ByVal strInput As String)
    For i = 1 To Len(strInput)  ' Do with each Character in input string...
        c = Mid(strInput, i, 1) ' Get actual char
        If IsNumeric(c) Or c = " " Then ' If numeric or a space then append to output
            KeepNumbersAndSpaces = KeepNumbersAndSpaces & c
        End If
    Next
End Function

Sub Test()
    strInput = "qwert1234*90)! qwe123 4567*. 90"
    MySplitter (strInput)
    Range("A5") = KeepNumbersAndSpaces(strInput)
End Sub

回答by brettdj

Something like this to

像这样的东西

• split the string using a regexp
• place the matches into an array
• dump the array to an automatically sized spreadsheet range

• 使用正则表达式拆分字符串
• 将匹配项放入数组中
• 将数组转储到自动调整大小的电子表格范围

main sub

主副

Sub CleanStr()
Dim strOut As String
Dim Arr
strOut = Trim(KillChar("qwe123 4567*. 90 "))
Arr = Split(strOut, Chr(32))
[a1].Resize(UBound(Arr) + 1, 1) = Application.Transpose(Arr)
End Sub

function

功能

Function KillChar(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "[^\d\s]+"
        KillChar = .Replace(strIn, vbNullString)
    End With
End Function