I am trying to remove leading whitespace from a word " 00000000000000231647300000000002KK".

Below is my VBA code

Option Explicit

Sub myfunction()
    Dim getarray, getarray1 As Variant
    Dim Text As String
    Dim RegularText
    getarray = Sheets("Sheet1").Range("A1:A4").Value
    getarray1 = getarray
    Set RegularText = New regexp
    RegularText.Global = True
    RegularText.MultiLine = True
    RegularText.Pattern = "(^\\s )"
    Text = CStr(getarray(1, 1))
    getarray1(1, 1) = RegularText.Replace(getarray(1, 1), "")
    Sheets("Sheet1").Range("B1:B4").Value = getarray1
End Sub

However above code fails to remove the leading whitespace from my word.

Below is the excel workbook with result and above code https://easyupload.io/jv6n2p

If you could help to understand why my code is failing to remove leading whitespace, it will be very helpful.

Thanks for your time

CodePudding user response：

There are a few things wrong with the original code.

 RegularText.Pattern = "(^\\s )"

Explanations from regex101.com.

(^\\s ) pattern:

Basically, the first backslash is escaping the second backslash. This tells the RegEx to treat the second \ as a normal character. (^\\s ) is grouping leading \s characters together not whitespace.

(^\s ) pattern:

 RegularText.MultiLine = True

The MultiLine property indicates every line in a value should be searched not row in an array. This doesn't seem to be the intended result. So set it to false.

`RegularText.MultiLine = False`  

Range("A1:A4").Value is 1 row by 4 columns and Range("B1:B4") is 1 column by 4 rows. In my examples I will use Range("A2:D2") for simplicity.

Sub RegExRemoveTrailingSpace()
    Dim Data As Variant
    Data = Sheets("Sheet1").Range("A1:A4").Value
    
    Dim RegularText As New RegExp
    RegularText.Global = False
    
    RegularText.Pattern = "(^\s )"
    [b4] = RegularText.Replace([A1], "")
    Dim r As Long, c As Long
    For r = 1 To UBound(Data)
        For c = 1 To UBound(Data, 2)
            Data(r, c) = RegularText.Replace(Data(r, c), "")
        Next
    Next
    
    Sheets("Sheet1").Range("A2:D2").Value = Data
    
End Sub

We could just use LTrim() to remove the leading spaces from the string.

Sub LTrimTrailingSpace()
    Dim Data As Variant
    Data = Sheets("Sheet1").Range("A1:A4").Value

    Dim r As Long, c As Long
    For r = 1 To UBound(Data)
        For c = 1 To UBound(Data, 2)
            Data(r, c) = LTrim(Data(r, c))
        Next
    Next
    
    Sheets("Sheet1").Range("A2:D2").Value = Data
    
End Sub