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

 

[Steve Hartman]

Well first off, you can replace all those X- replaces with just one. Search for the hyphen and leave the replace with blank.

As for the leading zero, do all tthe items have a leading zero or can there be another number there?

 

[harrysolomon]

No, all the items do not have a leading zero.
The leading position can be any number between 0 and 9.
This message was edited by harrysolomon on 2002-03-07 10:01

 

[Steve Hartman]

Okay, are all the numbers the same length?

 

[Steve Hartman]

I'm going to assume the numbers are all the same length. Search for the hyphen and replace with nothing. then format your cells with the custom format 000000000000. (that's 12 zeros)

Should fix you up

 

[harrysolomon]

First of all, Yes the numbers are always 12 characters.

Second, your solution works perfectly.

Thanks for your help!

 

[Mark W.]

On 2002-03-07 11:16, harrysolomon wrote:
First of all, Yes the numbers are always 12 characters.

Second, your solution works perfectly.

Thanks for your help!

However, Steve's recommendation will convert your bar code to a numeric data type that is stored without the leading zero. Formatting only governs the display of the value. If you intend to search or conditionally test for "075678214424" you won't find it. Because of this and because you won't be performing arithmetic operations on these values I strongly suggest that you format the bar codes as Text before you replace the hyphens! Doing so will preserve the leading zeroes as well as the textual nature of your bar code.
This message was edited by Mark W. on 2002-03-07 11:31

 

[Steve Hartman]

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.

He's already tried formatting as text. I did also, and removing the hyphens drops the leading zero.

 

[Steve Hartman]

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.

He's already tried formatting as text. I did also, and removing the hyphens drops the leading zero.

 

[harrysolomon]

Ultimately, the spreadsheet is going to be saved as a *.prn file which will then be used as a COBOL sequential file. I'm writing a report which will read this UPC# and use it to test for other values. My original goal here was to be able to convert a UPC such
as "0-7567-82144-2-4" to "075678214424".