TextToColumns, VBA to uncheck "Treat consecutive delimiters as one"

[realtoast]

The below code parses a single column address to dedicated columns for Address1, Address2, City, State, Zip. The code first tests the original column and places commas so that TextToColumns can do its work with commas as the delimeter. My problem is, Excel keeps returning the TextToColumns selection for "Treat consecutive delimiters as one" as True. This causes the function to fail because, where people do not have an apartment number (Address2), the code places two commas. With the above checkbox selected as True, TTC ignores what should be a blank cell and moves City to where the blank cell should be. It basically shuffles the output.

    Application.DisplayAlerts = False
    Columns("O:W").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Range("N1", Range("N" & Rows.Count).End(xlUp)).Columns(3)
        .Columns("O:W").ClearContents
        .Value = Evaluate(Replace("if(len(#)-len(substitute(#,"","",""""))<4,substitute(#,"","","",,"",1),if(#<>"""",#,""""))", "#", .Columns(-1).Address))
        .TextToColumns .Cells(1), 1, comma:=True, Tab:=False, Space:=False
        .CurrentRegion.Columns.AutoFit
    End With

I've tried unchecking the box before running the script, but it keeps returning to True.

 

[MARK858]

ConsecutiveDelimiter:=False

 

[dreid1011]

Here is the code I get from recording my actions for TTC:

Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

 

[Joe4]

Try adding that argument directly into your script, i.e.
change this line:

        .TextToColumns .Cells(1), 1, comma:=True, Tab:=False, Space:=False

to this:

        .TextToColumns .Cells(1), 1, ConsecutiveDelimiter:=False, comma:=True, Tab:=False, Space:=False

 

[realtoast]

Try adding that argument directly into your script, i.e.
change this line:

        .TextToColumns .Cells(1), 1, comma:=True, Tab:=False, Space:=False

to this:

        .TextToColumns .Cells(1), 1, ConsecutiveDelimiter:=False, comma:=True, Tab:=False, Space:=False

Thank you. This works!

 

[Joe4]

You are welcome.
Glad we could help (we were all really saying the same thing!)