UPC Bar Code info in Excel

[harrysolomon]

I have a column which contains UPC Bar Code information, for example 0-7567-82144-2-4.
I need to remove the hyphens from the bar code info, while preserving the leading zero.
I wrote a macro to do this by using EDIT|REPLACE and replacing "0-" with "0", "1-" with "1" etc, up through "9-".
However this causes the leading zero to disappear. I've tried changing the format of the column prior to using EDIT|REPLACE but the zero is discarded no matter what format I use.
Can somebody please tell me how to accomplish
this?

Thanks
This message was edited by harrysolomon on 2002-03-07 09:29

 

[Mark W.]

Then it shouldn't matter which approach you use.

 

[Steve Hartman]

If you have to have it as text you could use:

=IF(LEN(A1)=12,A1,"0"&A1)

 

[harrysolomon]

Yes, technically I could format the spreadsheet in the COBOL program. But
I was trying to get as much of the formatting
of the information as possible done before creating the *.prn file.

 

[Don C]

So the question before the court is whether you want the UPC stored as a 12 character text entry, or whether the entries can be true numbers which are DISPLAYED as 12 digits regardless the the true number of digits.

When I send data to our COBOL machine, I have to change every to fixed length text. In this case, you would REPLACE the hyphen with a null (blank), then use =TEXT(a2,"000000000000") to convert the field to true text with 12 real characters; copy down the spreadsheet, then highlight the TEXT formulas, copy, and paste/special/values to remove the formulas. I usually overwrite the original data, but that is your choice.

When you remove the hypens, Excel sees the entry as a number. If numbers are what you want, as noted above you can create a custom format so that leading zeros are displayed even though they are not present in the number stored in the cell.

 

[harrysolomon]

Storing the UPC as a 12 character text entry
seems to work.
The solution I've settled on, is to select the column with the UPC data, replace the hyphens and then format the column as Custom '000000000000'.
This keeps the leading 0 in place, and it appears to work in the sequential file (I've defined the UPC field in the file as pic x(12)).