Dealing with colon separated values when values themselves contain colons

This will fix your import providing only the username could have one or more colons. Explanations are in code comments. I chose working with an array because it's faster than working on the worksheet and your sample data showed at least 47K entries. If any other import field could have colons then you will need to rework it to compensate for them.

Option Explicit

Sub Fix_Import()

    Dim i As Long, j As Long, arr As Variant

    'work with the data area expanding outward from Range("A1")
    With Worksheets("sheet1").Cells(1).CurrentRegion

        'if there are no split usernames  then don't continue
        If .Columns.Count = 4 Then Exit Sub

        'collect the imported values (minus the column headers) into an array
        arr = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Value2

    End With

    'loop through the 'rows' of the array
    For i = LBound(arr, 1) To UBound(arr, 1)

        'when everything is correct, the 5th array element will be blank
        Do While arr(i, 5) <> vbNullString

            'loop through this 'row' of the array
            For j = 1 To UBound(arr, 2)

                'do different stuff depending on which of the array's 'columns' you are working on
                Select Case True
                    Case j = 1
                        arr(i, j) = Join(Array(arr(i, j), arr(i, j + 1)), Chr(58)) 'join on colon
                    Case j < UBound(arr, 2)
                        arr(i, j) = arr(i, j + 1) 'make this array element the same as the next
                    Case j = UBound(arr, 2)
                        arr(i, j) = vbNullString 'make the final element a null string
                End Select

            Next j
        Loop

    Next i

    'put the array elements back on the worksheet
    Worksheets("sheet1").Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr

End Sub

<sub>Before

After</sub>