PDA

View Full Version : Solved: Inserting '-' in cell items



Marcster
09-30-2005, 06:56 AM
Hi people :hi: ,

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?. :help

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

Thanks,

Marcster.

Norie
09-30-2005, 09:47 AM
Try this.

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

lucas
09-30-2005, 11:54 AM
Nice one Norie:thumb

Marcster
10-02-2005, 01:30 AM
Hi Norie :hi:,

Thanks alot, works great :yes :clap:,

Marster.