Multiple character delimiter for text to column function

[Rooza]

Hello,

I have a column of data with the fields separated by differing combinations of commas, semi-colons, multiple spaces and tabs. The only thing the separations have in common is that they all incorporate more than one space.

I would like to use the text to columns feature to separate the data into columns, however the built-in feature only lets me use single spaces as a delimiter, which is problematic as some of the data within the fields has spaces in it.

The end product doesn't need to be tidy, it just needs to have the data in the correct columns. As each separator has between 5 and 9 spaces in it, using 5 spaces as the delimiter would separate the data sufficiently. Any help will be appreciated!

 

[wigi]

Hi there

Welcome to MrExcel !

Try:

Sub splitit()

    With ActiveSheet.UsedRange.Columns(1)
        .Replace Space(5), "§", xlPart
        .TextToColumns other:=True, OtherChar:="§"
    End With

End Sub

 

[pgc01]

Hi
Welcome to the board

... As each separator has between 5 and 9 spaces in it, using 5 spaces as the delimiter would separate the data sufficiently....

Before using Text to Columns select the column and replace 5 spaces with "#" (or any other character that you are sure that does not already appear in the cells)

In the second panel of the Text to Columns feature check all the Tab, semicolon, comma checkboxes. Check also the "Other" checkbox and in its textbox write the "#".
Then check also the checkbox "Treat consecutive delimiters as one"

Please try.

 

[Rooza]

First of all, thanks for your help,

Wigi - I tried the code but certain rows replaced gaps with "§" and didn't split the fields into columns, not sure why these rows did this when other similar ones didn't.

pgc01 - I tried this however the replace function replaced each space with a "#" instead of replacing each group of 5 consecutive spaces, resulting in many blank rows between fields

 

[pgc01]

pgc01 - I tried this however the replace function replaced each space with a "#" instead of replacing each group of 5 consecutive spaces, resulting in many blank rows between fields

Hi

I don't see how that can happen.

If I specify to replace 5 spaces with something else, that's what happens.

 

[wigi]

My suggestion (which is what pgc01 is saying too):

Sub splitit()

    With ActiveSheet.UsedRange.Columns(1)
        On Error Resume Next
        Do
            v = WorksheetFunction.Match("*" & Space(5) & "*", .Cells, 0)
            If Err Then Exit Do
            .Replace Space(5), "§", xlPart
        Loop
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, ConsecutiveDelimiter:=True, other:=True, OtherChar:="§"
    End With


End Sub

 

[István Hirsch]

This works if there is only one space in the 'normal' text.
First, replace _ _ (2 spaces) with # then replace #_ with #, then perform TexttoColumns with # as delimiter, 'Treat consecutive..' checked.