How to remove leading spaces and/or "/" or "\"

Question

I am new to VBA in Excel. I have a spreadsheet that always has 172 columns. A:FP. It may have 2 to many rows. For every cell in the spreadsheet, I want to remove all leading and trailing spaces along with any leading or trailing "/" or "\". A string can contain a back or forward slash, just not at the beginning or end of the string. My code removes leading and trailing blank spaces. It removes trailing back or forward slashes. However, it does not remove leading slashes. I cannot understand why. Is there a better way of accomplishing my goal? Thank you for your help.

For Each Rng In ActiveSheet.UsedRange
   CellVal = Trim(Rng.Value)
   LengT = Trim(Len(CellVal))

      If CellVal <> "" Then
         If Not Rng.HasFormula Then
            While Trim(Left(CellVal, 1) = Chr(47)) Or Trim(Left(CellVal, 1) = Chr(92)) Or Trim(Right(CellVal, 1) = Chr(47)) Or Trim(Right(CellVal, 1) = Chr(92))

            If Trim(Left(CellVal, 1) = Chr(47)) Or Trim(Left(CellVal, 1) = Chr(92)) Then
               CellVal = Trim(Mid(CellVal, 2, LengT))
            ElseIf Trim(Right(CellVal, 1) = Chr(47)) Or Trim(Right(CellVal, 1) = Chr(92)) Then
               LengT = Len(CellVal)
               CellVal = Trim(Left(CellVal, LengT - 1))
               Rng.Value = CellVal
               LengT = LengT - 1
            End If
         Wend
      End If
   End If
Next Rng

score 0 · Answer 1 · answered May 22 '20 at 08:19

First, I'd say try to avoid UsedRange but find a way to get your last used row and column. Then, instead of making a ton of calls to the Worksheet object, I'd run over your values in memory through the use of an array.

Last, you can do many replacements etc. But maybe using a regular expression isn't out of place here. Just for inspirations, I'll put down the following:

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim lr As Long, lc As Long, x As Long, y As Long
Dim arr As Variant
Dim RegEx As Object: Set RegEx = CreateObject("vbscript.regexp")

'Set up regular expression first
RegEx.Global = True
RegEx.Pattern = "^[ \/\\]*(.*?)[ \/\\]*$"

With ws
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    arr = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            If RegEx.Test(arr(x, y)) Then arr(x, y) = RegEx.Execute(arr(x, y))(0).submatches(0)
        Next
    Next
    .Range(.Cells(1, 1), .Cells(lr, lc)).Value = arr
End With

End Sub

So in the end it's reading back the cleaned data to the worksheet.

Regex Demo

Hello JvdV. Yes!!! It worked beautifully. I had some trouble with the "Set ws =", but that was a me problem. Once I replaced "Sheet1" with the name that my macro gives the worksheet, it worked perfectly. And the link to the Regex Demo is a real bonus. It really helps explain who to use regex. I will play with some regex values to dome even more cleanup. Thank you , thank you, thank you! — Gianmarco, May 24 '20 at 00:09
@Gianmarco, glad it helped. Don't forget to accept the answer to return the favour. Click the check button to it's left. If you gathered enough points it will even let you upvote it — JvdV, May 24 '20 at 07:15

How to remove leading spaces and/or "/" or "\"

1 Answers1