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

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-

ExcelMonkey wrote:
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

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.

JW wrote:
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-

ExcelMonkey wrote:
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

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


"ExcelMonkey" wrote in message
...
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

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


"ExcelMonkey" wrote in message
...
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

Thanks all. Slick Stuff!

EM

"Norman Jones" wrote:

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


"ExcelMonkey" wrote in message
...
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

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



"Norman Jones" wrote:

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


"ExcelMonkey" wrote in message
...
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

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


"RB Smissaert" wrote in message
...
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


"ExcelMonkey" wrote in message
...
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

On Wed, 9 May 2007 13:14:01 -0700, ExcelMonkey
wrote:

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