Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

excel - Script to remove letters

I need an Excel VB script to remove all letter a-z A-z and () from cell.

I want to keep all numbers and periods (.).

For example SDF dsfsd dfS SD ( dfd ))) sdf 2.1 mg uf g will become 2.1

This is what I have but it is not working:

Function strClean(strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "(([0-9]).)"
outputStr = objRegExp.Replace(strtoclean, "-")
objRegExp.Pattern = "-+"
outputStr = objRegExp.Replace(outputStr, "-")
strClean = outputStr
End Function
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Try this:

Sub Remove_Alpha(rCll As Range)
Dim sCll As String, i As Integer
    sCll = rCll.Value2
    sCll = WorksheetFunction.Clean(sCll)
    For i = 1 To 255
        Select Case i
        Case 46, 48 To 57
        Case Else
            sCll = WorksheetFunction.Substitute(sCll, Chr(i), "")
        End Select
    Next
    rCll.Value = sCll
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...