UPC Bar Code info in Excel

harrysolomon

New Member
Joined
Mar 6, 2002
Messages
25
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
First of all, Yes the numbers are always 12 characters.

Second, your solution works perfectly.

Thanks for your help!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top