Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: UPC Bar Code info in Excel

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Second, your solution works perfectly.

    Thanks for your help!




  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Taxation WITH representation ain't so hot either

  9. #9
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Taxation WITH representation ain't so hot either

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •