Uncompressing UPC version E barcodes in Excel
Azalea_UPC
Copyright 2009 Azalea Software, Inc. All rights reserved. www.azaleabarcodes.com


The macro in this spreadsheet converts 7-digit UPC version E barcodes into 12-digit UPC numbers. The macro accepts 7-digit input as a string and returns the company prefix + unique product number + UPC version A check digit.

Because this spreadsheet is built around a macro, you *must* enable macros for this spreadsheet to work! The .xls file is for Excel 2003 and the .xlsm is for Excel 2007. An alternative is to use a User Defined Function as an .xla (Excel 2003) or .xlam (Excel 2007).

Press ALT-F11 to view the macro in the Visual Basic Editor.

To add the macro to your own spreadsheet:
  Tools/Macro/Visual Basic Editor
  Insert/Module
  Paste in the macro code
  Close the Visual Basic Editor

When you return to your spreadsheet, a new User Defined function is available: Azalea_versionEundo

UPCTools prints UPC, ISBN-13, EAN, JAN, and ISSN barcodes. Available for Windows, OS X, Linux/UNIX, et al. Free sample code and free tech support. Buy online and download immediately.

www.azaleabarcodes.com/UPC


Function Azalea_versionEundo(ByVal versionE As String) As String
' UPCTools 16mar09 jwhiting
' Copyright 2009 Azalea Software, Inc. All rights reserved. www.azaleabarcodes.com

' Uncompress a UPC version E back into the original 12-digit UPC version A with check digit in Excel 2003
' Your input, versionE, is a string consisting of a 7-digit number.

  Dim checkDigitSubtotal As Integer     ' a check digit subtotal
  Dim checkDigit As String              ' the check digit itself
  Dim temp As String                    ' a temporary placeholder
  
' convert version E to version A
Select Case Val(Right$(versionE, 1))
  Case 0
    temp = Left$(versionE, 3) + "00000" + Mid$(versionE, 3, 3)
  Case 1
    temp = Left$(versionE, 3) + "10000" + Mid$(versionE, 3, 3)
  Case 2
    temp = Left$(versionE, 3) + "20000" + Mid$(versionE, 3, 3)
  Case 3
    If Mid$(versionE, 4, 1) = "0" Or Mid$(versionE, 4, 1) = "1" Or Mid$(versionE, 4, 1) = "2" Then
      'error message X3
    End If
    temp = Left$(versionE, 4) + "00000" + Mid$(versionE, 4, 2)
  Case 4
    If Mid$(versionE, 5, 1) = "0" Then
      'error message X4
    End If
    temp = Left$(versionE, 5) + "00000" + Mid$(versionE, 5, 1)
  Case 5 To 9
    If Mid$(versionE, 6, 1) = "0" Then
      'error message X5
    End If
    temp = Left$(versionE, 6) + "0000" + Right$(versionE, 1)
  End Select

  ' Calculate the UPC version E check digit.
  checkDigitSubtotal = 3 * (Val(Left$(temp, 1)) + Val(Mid$(temp, 3, 1)) + Val(Mid$(temp, 5, 1)) + Val(Mid$(temp, 7, 1)) + Val(Mid$(temp, 9, 1)) + Val(Right$(temp, 1)))
  checkDigitSubtotal = checkDigitSubtotal + Val(Mid$(temp, 2, 1)) + Val(Mid$(temp, 4, 1)) + Val(Mid$(temp, 6, 1)) + Val(Mid$(temp, 8, 1)) + Val(Mid$(temp, 10, 1))
  checkDigit = Right$(Str$(300 - checkDigitSubtotal), 1)

  ' Concatenate the check digit after the 11-digit company prefix & product ID
  Azalea_versionEundo = temp + checkDigit

  ' Excel: B1=Azalea_Azalea_versionEundo(A1)
  ' Or put another way, yourContainer.text=Azalea_versionEundoo(yourInputString)
  
End Function