Can you restrict the used columns in VBA TextToColumns so that it won't overwrite other data?

[9tanstaafl9]

I frequently have to clean up phone numbers for data import. I got everything to work perfectly, EXCEPT for the rare instances where there might be more than one delimiter between a phone number and an extension. So these work fine:

444-444-4444 ext: 2345
333-1111 #2224456 cindy

But this doesn't

333-333-3333 ##2345

In the latter case, the TextToColumns would separate the data into 3 columns not two, and would overwrite an entire column of data that I need to keep.

So my question, is there any way to modify my code so that it can't split data into more than the selected column and the column to the right?

'attempts to split out extensions entered into phone field
On Error Resume Next
    With Selection
        .Replace "~*", "§", xlPart
        .Replace "#", "§", xlPart
        .Replace "extension", "§", xlPart
        .Replace "ext:", "§", xlPart
        .Replace "ext", "§", xlPart
        .Replace "ex", "§", xlPart
        .Replace "x", "§", xlPart
        .Replace ":", "§", xlPart
        .TextToColumns other:=True, OtherChar:="§"
    End With
    
'Removes nonnumeric cells and formats number correctly
On Error Resume Next
    Dim c As Range
    Dim i As Long
    Dim sTemp As String
    If Not TypeOf Selection Is Range Then Exit Sub
    For Each c In Selection
        sTemp = vbNullString
        For i = 1 To Len(c.Value)
            If IsNumeric(Mid(c.Value, i, 1)) Then sTemp = sTemp & Mid(c.Value, i, 1)
        Next
        c.Value = sTemp
        If Len(c) = 11 Then c = Right(c, 10)
        c.NumberFormatLocal = "000 000-0000"
    Next

I don't care how the extension field will be formatted. I just want to save the information so that it is available.

 

[Rick Rothstein]

Add a call to the Replace method in the beginning area of your code that replaces "##" with "#" and, if you think there might be three of them, that replaces "###", "#"... after that, the rest of your code should work the same as it does now.

 

[AlphaFrog]

There is a Consecutive Delimiter argument for TextToColumns.

ConsecutiveDelimiter Optional Variant. True to have Microsoft Excel consider consecutive delimiters as one delimiter. The default value is False.

.TextToColumns other:=True, OtherChar:="§", ConsecutiveDelimiter:=True

 

[9tanstaafl9]

Add a call to the Replace method in the beginning area of your code that replaces "##" with "#" and, if you think there might be three of them, that replaces "###", "#"... after that, the rest of your code should work the same as it does now.

Thanks for the quick reply. That is a great suggestion and that IS what I will do unless someone tells me there is a way to restrict the TextToColumns so it can't overlap my data with unforeseen extra columns.

The downside to your method is that it is a freeform field, so users can literally type in anything, and you wouldn't believe the strange things I have seen. There could be many different and multiple delimiters.

I think I can solve the issue by inserting an extra column to the right, filling it with something and then after the macro runs I can check to see if that something is still there (it wouldn't be if the column was overwritten). Then I could pop up a text box saying the whole thing didn't work and I need to revert to the last version. It is fugly so I didn't want to do it that way.

Thanks again for your help. I love this board!

 

[AlphaFrog]

Did you see post #3 regarding the Consecutive Delimiter argument?

 

[9tanstaafl9]

Hurray! That's what I was hoping for. And no, I didn't see that, I was busy typing my long-winded reply. (Is there an equivalent term for long-winded when used for typing? I realize that makes no sense.)