VB add characters in a cell

[weenie]

Hello,

Using Excel 2007. I have data in Col. B that will always have 12 characters (characters will always vary).
1) How do I add dashes & period at specific spots in name Col B cell 2
2) then copy this new name all way down to last cell in col B?

Example:

417331450200

I want to add two dashes & one period 417-33-1450.200

Then take new name & copy down to last cell of column.

I don't need it to loop.

I have no idea how to write vb code to add these characters.

Thanks,
weenie

[YasserKhalil]

Hello Try this code

Sub Test()    Dim arr     As Variant
    Dim i       As Long
    Dim v       As String
    Dim x       As String
    Dim y       As String
    Dim z       As String
    Dim s       As String
    
    arr = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).Resize(, 2).Value
    For i = LBound(arr, 1) To UBound(arr, 1)
        If Not IsEmpty(arr(i, 1)) Then
            v = Left(arr(i, 1), 3)
            x = Mid(arr(i, 1), 4, 2)
            y = Mid(arr(i, 1), 6, 4)
            z = Right(arr(i, 1), 3)
            s = v & "-" & x & "-" & y & "." & z
            arr(i, 2) = s
        End If
    Next i
    Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).Resize(, 2).Value = arr
End Sub

[weenie]

Thank you so much! It works!!

weenie

[YasserKhalil]

You're welcome. Glad I can offer some help
Regards

[mikerickson]

Would it work for you if you just formatted the cells with the custom format 000-00-0000.000

[weenie]

Didn't think of in that sense. The above code worked with no issues. I'm always open to learning different methods

weenie