UPC Bar Code info in Excel

harrysolomon · Mar 7th, 2002, 10:28 AM

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 · Mar 7th, 2002, 10:53 AM

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 · Mar 7th, 2002, 11:00 AM

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 · Mar 7th, 2002, 11:05 AM

Okay, are all the numbers the same length?

Steve Hartman · Mar 7th, 2002, 11:46 AM

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 · Mar 7th, 2002, 12:16 PM

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

Second, your solution works perfectly.

Thanks for your help!

Mark W. · Mar 7th, 2002, 12:30 PM

Quote:

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 · Mar 7th, 2002, 12:35 PM

Quote:

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 · Mar 7th, 2002, 12:35 PM

Quote:

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 · Mar 7th, 2002, 12:37 PM

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".

Mar 7th, 2002, 10:53 AM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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? __________________ Veni, Vidi, Velcro - I came, I saw, I stuck around Taxation WITH representation ain't so hot either

Mar 7th, 2002, 11:05 AM	#4
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Okay, are all the numbers the same length? __________________ Veni, Vidi, Velcro - I came, I saw, I stuck around Taxation WITH representation ain't so hot either

Mar 7th, 2002, 11:46 AM	#5
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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 __________________ Veni, Vidi, Velcro - I came, I saw, I stuck around Taxation WITH representation ain't so hot either

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 10:28 AM	#1
harrysolomon New Member Join Date: Mar 2002 Posts: 12	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 ]

Mar 7th, 2002, 11:00 AM	#3
harrysolomon New Member Join Date: Mar 2002 Posts: 12	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 ]

Mar 7th, 2002, 12:16 PM	#6
harrysolomon New Member Join Date: Mar 2002 Posts: 12	First of all, Yes the numbers are always 12 characters. Second, your solution works perfectly. Thanks for your help!

Mar 7th, 2002, 12:37 PM	#10
harrysolomon New Member Join Date: Mar 2002 Posts: 12	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".