CODEWITHSUNDEEP
regexexcelvba
Cam
Cam

Reputation: 3

Regex to move cells based on leading spaces

Ill start by saying that I'm not a coder, only someone who very rarely dabbles to make spreadsheets slightly more bearable.

I currently have some data that I need to break out into columns based on the number of leading spaces in the cell. Basically, if the cell begins with 2 spaces move it 1 column to the right, If there are 3 spaces, move it 2 columns to the right and so on.

I realised that I would need to use regex for this as FIND and LEFT would match all of the 3 space cells when searching for 2 space cells.

So I searched around and cobbled together this mess

Sub MoveStuff()
Dim RE as Object
Dim LSearchRow As Long
Dim LCopyToColumn As Long

Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "  (a-zA-Z)"

LSearchRow = 2

While Len(Cells(LSearchRow, "B").Value) > 0

If RE.Test(Cells(LSearchRow, "B").Value) Then

Up to here, it will match correctly, but I don't know how to get it to shift the cell over. Then I'll obviously need to have multiple RE.Patterns and If statements to match 3 and 4 space cells

Upvotes: 0

Views: 56

Answers (1)

Ass3mbler
Ass3mbler

Reputation: 3915

A general solution is the following. You count the leading spaces (let's call this value N), then remove them from your cell value and copy the cell N column on the right.

Public Sub movestuff()

Dim curr_row, curr_column, s

curr_column = 2 'COLUMN "B"
curr_row = 1


While (ActiveSheet.Cells(curr_row, curr_column) <> "")

    s = ActiveSheet.Cells(curr_row, curr_column)

    For x = 1 To Len(s) Step 1

        If Mid(s, x, 1) <> " " Then
            Exit For
        End If
    Next

    s = Mid(s, x)

    ActiveSheet.Cells(curr_row, curr_column + (x - 1)) = s

    curr_row = curr_row + 1

Wend
End Sub

Upvotes: 1

Related Questions