Remove Non-Numerics from String

[Guest]

I need a function to remove "$" and all capitalized letters (i.e. A, B, etc)
from a string variable. Assuming that regular expressions is the way to to
using the Replace method. Any ideas on how to do this via VBA?

Thanks

EM

 

[JW]

Certainly.
Function LowerText(sText As String) As String
Dim i As Integer
For i = 1 To Len(sText) 'from first to last characters
If Mid(sText, i, 1) Like "[a-z]" Then _
LowerText = LowerText + Mid(sText, i, 1)
Next i
LowerText = LowerText
End Function

HTH
-Jeff-

 

[JW]

Well, what I posted above will extract only the lower case letters fro
the string. The Like clause can be tweaked to your specific criteria.

Certainly.
Function LowerText(sText As String) As String
Dim i As Integer
For i = 1 To Len(sText) 'from first to last characters
If Mid(sText, i, 1) Like "[a-z]" Then _
LowerText = LowerText + Mid(sText, i, 1)
Next i
LowerText = LowerText
End Function

HTH
-Jeff-

I need a function to remove "$" and all capitalized letters (i.e. A, B, etc)
from a string variable. Assuming that regular expressions is the way to to
using the Replace method. Any ideas on how to do this via VBA?

Thanks

EM

 

[RB Smissaert]

Not sure if you can improve much on something simple like this:

Sub ReplaceUpperAndDollar(strString As String, Optional strReplace As
String)

Dim i As Long
Dim strFind As String

strFind = "ABCDEFGHIJKLMNOPQRSTUVWXYZ$"

For i = 1 To Len(strFind)
strString = Replace(strString, Mid$(strFind, i, 1), strReplace, 1, -1,
vbBinaryCompare)
Next

End Sub


I would only bother with something more complex if speed really is
important.

RBS

 

[Norman Jones]

Hi ExcelMonkey,

Try:

'=============>>
Public Sub TestIt()
Const aStr As String = "Aa$1Bb2$Cc3d$Dd4$"
MsgBox NoUcasePlus(aStr)
End Sub

'----------------->>
Public Function NoUcasePlus(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = False
.Global = True
oRegExp.Pattern = "[A-Z$]"
NoUcasePlus = .Replace(sStr, vbNullString)
End With
End Function
'<<=============

 

[Guest]

Thanks all. Slick Stuff!

EM

Hi ExcelMonkey,

Try:

'=============>>
Public Sub TestIt()
Const aStr As String = "Aa$1Bb2$Cc3d$Dd4$"
MsgBox NoUcasePlus(aStr)
End Sub

'----------------->>
Public Function NoUcasePlus(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = False
.Global = True
oRegExp.Pattern = "[A-Z$]"
NoUcasePlus = .Replace(sStr, vbNullString)
End With
End Function
'<<=============


---
Regards,
Norman

I need a function to remove "$" and all capitalized letters (i.e. A, B,
etc)
from a string variable. Assuming that regular expressions is the way to
to
using the Replace method. Any ideas on how to do this via VBA?

Thanks

EM

 

[Guest]

Norman, quick question for you. I want to incorporate what you provided with
another function I pulled from the newsgroup which splits an excel fomula by
operator. The first function is in example one below. Problem is, that it
does not preserve the absolute references of the cells ("$"). How do I
change the pattern do incorporate the "$"?

Example 1:
Function Parser(FormulaStr As String) As Variant
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Debug.Print Parsed(i, 1)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Debug.Print Parsed(i, 2)
Next i
End If

Parser = Parsed
End Function

Hi ExcelMonkey,

Try:

'=============>>
Public Sub TestIt()
Const aStr As String = "Aa$1Bb2$Cc3d$Dd4$"
MsgBox NoUcasePlus(aStr)
End Sub

'----------------->>
Public Function NoUcasePlus(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = False
.Global = True
oRegExp.Pattern = "[A-Z$]"
NoUcasePlus = .Replace(sStr, vbNullString)
End With
End Function
'<<=============


---
Regards,
Norman

I need a function to remove "$" and all capitalized letters (i.e. A, B,
etc)
from a string variable. Assuming that regular expressions is the way to
to
using the Replace method. Any ideas on how to do this via VBA?

Thanks

EM

 

[RB Smissaert]

This is faster:

Sub DeleteChars(strString As String, _
strOld As String, _
Optional strTemp As String = "¬")

Dim i As Long
Dim bFound As Boolean

For i = 1 To Len(strString)
If Mid$(strString, i, 1) Like strOld Then
Mid$(strString, i, 1) = strTemp
bFound = True
End If
Next

If bFound Then
strString = Replace(strString, strTemp, "", 1, -1, vbBinaryCompare)
End If

End Sub

and you would use it like this:

dim str As String

str = "|This is a test$AAA|"

DeleteChars str, "[A-Z$]"


Still, VBScript.RegExp is slightly faster, but only if you avoid repeatedly
declaring and setting the object.


RBS

 

[Ron Rosenfeld]

Norman, quick question for you. I want to incorporate what you provided with
another function I pulled from the newsgroup which splits an excel fomula by
operator. The first function is in example one below. Problem is, that it
does not preserve the absolute references of the cells ("$"). How do I
change the pattern do incorporate the "$"?

What do you mean when you write "does not preserve the absolute references of
the cells ("$")"?

I don't see it stripping out the "$"'s from cell references, at least in simple
formulas.

Example 1:
Function Parser(FormulaStr As String) As Variant
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Debug.Print Parsed(i, 1)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Debug.Print Parsed(i, 2)
Next i
End If

Parser = Parsed
End Function

--ron