Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find formatting in text in cell, insert tags around formatting. | Excel Programming | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro. | Excel Programming | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Formatting text in a user form text box | Excel Programming |