PDA

View Full Version : [SOLVED] VB add characters in a cell



weenie
07-07-2017, 01:05 PM
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
07-07-2017, 01:34 PM
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
07-07-2017, 02:29 PM
Thank you so much! It works!! :)

weenie

YasserKhalil
07-07-2017, 03:20 PM
You're welcome. Glad I can offer some help
Regards

mikerickson
07-07-2017, 03:20 PM
Would it work for you if you just formatted the cells with the custom format 000-00-0000.000

weenie
07-11-2017, 05:33 PM
Didn't think of in that sense. The above code worked with no issues. I'm always open to learning different methods

weenie