need help formatting text in macro

[Daniel M]

I have a spreadsheet that i scan barcodes into. Some of the barcodes are old
and omit the - in the serial number. I need to format the text to add it
back in. below is an example of the data. Also when the - is omitted it
reads the cell as text instead of general and omits the leading 0.

02-123456
2123456

You see no - and no leading 0. I need to add both back in. Can anyone give
me some help? I have tried a few things but i have another issue that breaks
all of them. Thanks.

dm.

 

[Gary Keramidas]

you could also use the mid function

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim cell As Range
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In ws.Range("A1:A" & lastrow)
If InStr(1, cell.Value, "-") = 0 Then
cell.Value = "0" & Left(cell.Value, 1) & "-" & _
Mid(cell.Value, 2, 30)
End If
Next
End Sub

 

[Gary Keramidas]

here's one way. i used sheet 1 and column A.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim cell As Range
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In ws.Range("A1:A" & lastrow)
If InStr(1, cell.Value, "-") = 0 Then
cell.Value = "0" & Left(cell.Value, 1) & "-" & _
Right(cell.Value, Len(cell.Value) - 1)
End If
Next
End Sub

 

[Don Guillett]

try

Sub addzeroanddash()
mc = "f"
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
If Left(Cells(i, mc), 1) <> 0 Then
Cells(i, mc) = "0" & Mid(Cells(i, mc), 1, 1) _
& "-" & Right(Cells(i, mc), 6)
End If
Next i
End Sub

 

[Rick Rothstein \(MVP - VB\)]

I've never worked with a bar code scanner before... I presume it put the
values it reads into the cells as text (or possibly numeric) constants. If
so, give this a try...

Sub AddZeroAndDash()
Dim C As Range
For Each C In Columns("F").SpecialCells(xlCellTypeConstants)
C.Value = Format(Replace(C.Value, "-", ""), "00-000000")
Next
End Sub

Rick