Excel Text Split with one condition using VBA

Question

I've been able to split text in VBA to separate a series of numbers from one cell into 4 different cells. What I've been using works but I can't find a way to set a condition to ignore splitting it if the source cell isn't in a certain format.

When it's a series of numbers separated by a period, split the text. This already works. However, when it's just a number with no periods, I want it to ignore splitting it.

So for example,

5.10.4.3456 in C2 get split into 4 different cells starting on C14 = 5, C15=10, C16=4, C17=3456 and this works with no issue. However, when the number doens't have periods separating it (highlighted text in attachment), is there a way to ignore the split of the remaining cells in that column? The dataset will continue to grow every time we export a new set so the range will grow weekly.

I select an entire column using Range and then split it but there are more random single numbers that should be ignored (highlighted in attachment).

Range("C2:C12085").Select

Dim rng As Range
Set rng = Selection
rng.TextToColumns _
    Destination:=rng(1, 1).Offset(, 14), _
    TextQualifier:=xlTextQualifierDoubleQuote, _
    DataType:=xlDelimited, _
    SemiColon:=False, _
    Comma:=False, _
    Other:=True, _
    Space:=False, _
    OtherChar:="."
            

Answer 1

The text-to-columns split function requires a contiguous range of cells without gaps. But you can remove the unwanted cells from the destination range after splitting.

    Dim rng As Range, lastRow As Long
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range("C2:C" & lastRow)
    rng.TextToColumns _
        Destination:=rng(1, 1).Offset(, 14), _
        TextQualifier:=xlTextQualifierDoubleQuote, _
        DataType:=xlDelimited, _
        SemiColon:=False, _
        Comma:=False, _
        Other:=True, _
        Space:=False, _
        OtherChar:="."
        
    Dim arrs, arrd, i
    Dim rngD As Range
    arrs = rng.Value
    ' assuming text-to-column result in Q to T
    lastRow = Cells(Rows.Count, "Q").End(xlUp).Row
    Set rngD = Cells(2, "Q").Resize(lastRow - 1, 4)
    arrd = rngD.Value
    For i = 1 To UBound(arrs)
        If arrs(i, 1) = arrd(i, 1) Then arrd(i, 1) = ""
    Next
    rngD.Value = arrd
End Sub

I have tested the code with your sample data. There isn't any extra number in the next column.

---

Screenshot from OP user. Column P may contain data. The issue is fixed in the updated code.