You may want to try this

Sub add_zero_dash()

const_dash = "-"
'assuming data is in column A
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Range("a" & i).Select
entirename = ActiveCell.Value
check_len = Len(entirename)
If check_len = 8 Then
new_val = "0" & entirename
Selection.NumberFormat = "@"
ActiveCell.Value = new_val
new_val_first = Left(new_val, 3)
new_val_last = Right(new_val, 3)
new_val_mid = Mid(new_val, 4, 3)
ActiveCell.Value = new_val_first & const_dash & new_val_mid &
const_dash & new_val_last
End If
Next
End Sub


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=559431