need help formatting text in macro
 

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.

need help formatting text in macro
 

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


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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

--


Gary


"Daniel M" wrote in message
...
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.

need help formatting text in macro
 

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

--


Gary


"Daniel M" wrote in message
...
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.

need help formatting text in macro
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Daniel M" wrote in message
...
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.

need help formatting text in macro
 

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


"Daniel M" wrote in message
...
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.