Putting Regex into VBA code

Question

I asked a question a week or so ago about removing all white-space from cells in a column and copy/pasting them into the column next to it. I got some help from someone to help do this but the code doesn't always work. Which is fine but the more I use the code the more issues seem to rise up. The VBA code is using R TRIM as its operation for trimming white-space but that doesn't seem to always work. There always seems to be a weird white-space character that wont delete. The code I put together before I asked my original question did this but deploying it to different workbooks was not working.

Here is my original

Function simpleCellRegex(myRange As Range) As String

    Dim Regex As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "\s+$"

        If strPattern <> "" Then
            strInput = myRange.Value
            strReplace = ""

        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If Regex.Test(strInput) Then
            simpleCellRegex = Regex.Replace(strInput, strReplace)
        Else
            simpleCellRegex = strInput
        End If
    End If
End Function

Here is the code that I got from asking for help

Option Explicit
Public Sub RemoveEndWhiteSpace()
    Dim arr(), i As Long, myRange As Range
    Set myRange = Selection
    If myRange.Columns.Count > 1 Or myRange Is Nothing Then Exit Sub
    If myRange.Count = 1 Then
        myRange = RTrim$(myRange.Value)
        Exit Sub
    Else
        arr = myRange.Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 1) = RTrim$(arr(i, 1))
        Next i
        myRange.Offset(, 1) = arr
    End If
End Sub

I have tried multiple ways to put the Regex command into the working code but it always gives me an error. I tried to read up on VBA but I get nothing that really helps me. The reason I want it this way is because I work with huge amounts of data and the data can look very crazy. I want to clean up the crazy data by first removing the whitespace in each cell of the column, then outputting those cells into the column next to it. I would essentially like to combine the code but I have no idea how.

Could you show us the times that it doesnt work via a little snippet or picture? I'd like to try and understand how they're related — Marcucciboy2
– Marcucciboy2, Commented Jul 24, 2018 at 19:30
The code at the bottom is using RTRIM...That function removes trailing spaces but you may have carriage return, line feed, or some other non-printable ASCII character. You can look up online various functions that handle all of these in one go. You can also step through the length of the string and print the ascw code of each character and find out what characters you are dealing with and then use replace (for example) to swop them out for vbnullstring. — QHarr
– QHarr, Commented Jul 24, 2018 at 19:34
"deploying it to different workbooks was not working" please explain exactly what you mean here. — Tim Williams
– Tim Williams, Commented Jul 24, 2018 at 19:51
@QHarr ooh printing out the ascii value for each char is a good idea — Marcucciboy2
– Marcucciboy2, Commented Jul 24, 2018 at 19:52
White space not removed by \s is usually a NBSP: \xA0 And what is the error you see when you tried multiple ways to put the Regex command into the working code — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 25, 2018 at 1:17

Tim Williams · Accepted Answer · 2018-07-24 21:46:08Z

1

Addressing only the question of how to incorporate your function...

Untested:

Option Explicit
Public Sub RemoveEndWhiteSpace()
    Dim arr(), i As Long, myRange As Range
    Set myRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
    If myRange Is Nothing Then Exit Sub
    If myRange.Columns.Count > 1 Then Exit Sub

    If myRange.Count = 1 Then
        myRange.Offset(0,1).Value = simpleCellRegex(myRange.Value)
    Else
        arr = myRange.Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 1) = simpleCellRegex(arr(i, 1))
        Next i
        myRange.Offset(0, 1).Value = arr
    End If
End Sub


Function simpleCellRegex(v) As String

    Static Regex As RegExp

    'need to create/configure regex?
    If Regex Is Nothing Then
        Set Regex = New Regex
        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\s+$"
        End With
    End If

    If Regex.Test(strInput) Then
        simpleCellRegex = Regex.Replace(v, "")
    Else
        simpleCellRegex = v
    End If

End Function

edited Jul 24, 2018 at 21:46

answered Jul 24, 2018 at 19:35

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Williams Over a year ago

You select a range and run RemoveEndWhiteSpace - same as your current process

PaulG · Accepted Answer · 2018-07-25 12:08:04Z

0

'Replaces all data characters in the data range that are not defined in the pattern with the Replace character.

Public Sub RegExMatchAndReplace(ByVal Pattern As String, ByVal Data As Range, ByVal Replace As String)

'//////////////////////////////////////////////////////////////////////////

Dim oRegEx As New RegExp
Dim rPtr As Range

'///////////////////////////////////////////////////////////////////////////
For Each rPtr In Data
    With oRegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = Pattern
        rPtr.Value = oRegEx.Replace(rPtr.Value, Replace)
    End With
Next

End Sub

answered Jul 25, 2018 at 12:08

PaulG

1,2078 silver badges9 bronze badges

Collectives™ on Stack Overflow

Putting Regex into VBA code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related