Solved: Inserting '-' in cell items

[Marcster]

Hi people ,

In column A I have numbers like:
300003
560046
402520
205740
203964

Which I need converting to:
30-00-03
56-00-46
40-25-20
20-57-40
20-39-64
How many items in column A will vary.

Can anyone tell me a VBA macro in which do this please?.

Also I need a macro
to save this file as a text file without any file extension.
i.e. Filename: 300905

Thanks,

Marcster.

[Norie]

Try this.
[vba]
Sub Test()
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("A1:A" & LastRow)
Range("B1").Formula = _
"=LEFT(A1,2) &" & Chr(34) & "-" & Chr(34) & "& MID(A1,3,2)& " & Chr(34) & "-" & Chr(34) & " &RIGHT(A1,2)"
Range("B1").Copy rng.Offset(0, 1)

rng.Value = rng.Offset(0, 1).Value
rng.Offset(0, 1).ClearContents

ActiveSheet.Copy

ActiveWorkbook.SaveAs "c:\" & Format(Date, "ddmmyy"), xlTextWindows
ActiveWorkbook.Close True

Name "c:\" & Format(Date, "ddmmyy") & ".txt" As "c:\" & Format(Date, "ddmmyy")
End Sub
[/vba]

[lucas]

Nice one Norie

[Marcster]

Hi Norie ,

Thanks alot, works great ,

Marster.