Split Columns (delimter multiple space)

Hi All,

In Column A, I have a text data. Between words there is one space. I would like to split the column to multiple columns whenever there is more than one space between words (such that delimiter is 2 or more spcaes)

I thought that splitting the text when meeting more than one blank space could be a good idea.

Can Anyone assist with VBA Code please?

Many thanks,

Try

Sub test()
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\s{2,}"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            r = .Replace(r, Chr(2))
        Next
    End With
    Columns("a").TextToColumns [a1], 1, other:=True, otherchar:=Chr(2)
End Sub

thank you this works perfectly

Originally Posted by lilit2222

thank you this works perfectly

We do not know whose post you are referring to. You should make that clear for future readers of this thread.

Assuming your data is in Column A, give this macro a try...

Sub SplitOnMultipleSpaces()
  Columns("A").Replace Space(2), "|", xlPart, , , , False, False
  Columns("A").Replace "| ", "|"
  Columns("A").TextToColumns , xlDelimited, , True, False, False, False, False, True, "|"
End Sub

Hi and thanks,

Does this also take into account if there are two or more spaces?

Originally Posted by lilit2222

Does this also take into account if there are two or more spaces?

Not sure if you are asking me or jindon that question. Mine does and, while I haven't done anything with Regular Expressions in over 30 years, jindon's looks like it should. In any event, why not just try each of our routines and see. Either do that on a copy of your workbook or, if you do it on your live workbook, simply close it without saving if the code does not work.