making a UPC zero suppressed

[kenton71]

OK, so this should be pretty simple, but I can't figure it out.

I need to take a 12 digit UPC and come up with the 8 digit zero suppressed code. For those unfamiliar with UPC's, here's the way they are built:

A UPC is made up of a lead digit (in this case a "0"), a manufacture's code ("11000"), an item code ("00112") and a check digit ("6"). I need to keep this code in one column and have the zero suppressed code in the column next to it. A zero suppressed code is made by taking out the middle five 0's (can only be done if you have five 0's in a row) and then adding a "0" as the last digit before the check digit. The spaces between the lead and check digit are required for the zero suppressed code.

I have the following in Cell A:
0 11000 00112 6

I need to get to this in Cell B:
0 111120 6

 

[sheetspread]

This?

Excel 2010
	A	B
1	0 11000 00112 6	0 111120 6
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A1,3,12)," ",""),0,"")&0&" "&RIGHT(A1,1)

 

[Rick Rothstein]

Here is another formula you can consider...

=REPLACE(REPLACE(A1,14,0,0),5,6,"")

The above formula depends on the data containing the spaces you showed and on there always being 5 consecutive zeroes (ignoring for this count, the space between the groups) starting at 3 character position of the second group (what you called the manufacturer's code).

 

[sheetspread]

taking out the middle five 0's (can only be done if you have five 0's in a row)

My formula doesn't replace any instance of 5 zeroes in a row or separated by blanks in the middle two codes, maybe Rick's does, I'll try

 

[Rick Rothstein]

My formula doesn't replace any instance of 5 zeroes in a row or separated by blanks in the middle two codes, maybe Rick's does, I'll try

Actually, I'm not so sure my code does what the OP wants. Sure, it works for the one example the OP gave, but the line you quoted from him (which I missed initially), namely this...

A zero suppressed code is made by taking out the middle five 0's (can only be done if you have five 0's in a row)

and especially the red part, make me think there does not have to be 5 zeroes in a row and, if not, the OP has neglected to tell us what should be done. That is the problem when a posting gives a single example that appears not to be representative of all the possible data.

 

[DRSteele]

Try this. It decides whetehr or not there are five consecutive zeros and if there are it treats it as a Zero Suppress code, otherwise does nothing yet. Don't forget CTRL+SHIFT+ENTER.

I hope it works - it warped my brain for a second here!

Excel 2012
	A	B	C	D	E	F
1		creates array of all char	counts the zeros	is it Zero Supress		ANSWER
2	0 11000 00112 6	0	6	Zero Suppress	0 111120 6	0 111120 6
3	0 11653 50112 5	0	2	FALSE	FALSE	FALSE
Sheet40

Cell Formulas
Range	Formula
B2	=MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)
B3	=MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)
C2	=SUMPRODUCT(--((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))="0"))
C3	=SUMPRODUCT(--((MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))="0"))
E2	=IF(D2="Zero Suppress",LEFT(A2,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A2,3,12)," ",""),0,"")&0&" "&RIGHT(A2,1))
E3	=IF(D3="Zero Suppress",LEFT(A3,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A3,3,12)," ",""),0,"")&0&" "&RIGHT(A3,1))
D2	{=IF(MAX(FREQUENCY(IF(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)="0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ",""))))),IF(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)<>"0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))))))=5,"Zero Suppress")}
D3	{=IF(MAX(FREQUENCY(IF(MID(SUBSTITUTE(A3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))),1)="0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ",""))))),IF(MID(SUBSTITUTE(A3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))),1)<>"0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))))))=5,"Zero Suppress")}
F2	{=IF(IF(MAX(FREQUENCY(IF(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)="0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ",""))))),IF(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)<>"0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))))))=5,"Zero Suppress")="Zero Suppress",LEFT(A2,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A2,3,12)," ",""),0,"")&0&" "&RIGHT(A2,1))}
F3	{=IF(IF(MAX(FREQUENCY(IF(MID(SUBSTITUTE(A3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))),1)="0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ",""))))),IF(MID(SUBSTITUTE(A3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))),1)<>"0",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A3," ","")))))))=5,"Zero Suppress")="Zero Suppress",LEFT(A3,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A3,3,12)," ",""),0,"")&0&" "&RIGHT(A3,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sheetspread]

this should be pretty simple

a single example that appears not to be representative of all the possible data.

If we're converting UPC-A codes to UPC-E then more is required:

UPC Barcode Information and UPC A to UPC E Converter | TALtech

https://en.wikipedia.org/wiki/Universal_Product_Code


The first link does have a single line converter, as well as its source code in VB and JavaScript

 

[chicagocomputerclasses]

According to OPs explanation this should do it. This is solely based on what OP explained he wants to do, not UPC-A to UPC-E.

=LEFT(A1,1)&" "&SUBSTITUTE(MID(SUBSTITUTE(A1," ",""),2,10),"00000","")&" 0"&RIGHT(A1,1)

 

[chicagocomputerclasses]

Use this UDF for UPC-A to E

Public Function UPCA2UPCE(ByVal UPCpre As String) As String
    '
    ' Convert UPC-A to UPC-E format
    '
    ' Written by Glenn J. Schworak
    ' Slighly modified by me
    '
    UPCA = Replace(UPCpre, " ", "")
    
    Dim ValidDigits As String
    Dim Mfg As String
    Dim Prod As String
    Dim x As Integer
    
    If Len(UPCA) <> 12 Or (Left(UPCA, 1) <> "0" And Left(UPCA, 1) <> "1") Or _
       InStr(1, Mid(UPCA, 5, 8), "0000") < 1 Then
        UPCA2UPCE = "N/A"
    Else
        Mfg = Mid(UPCA, 2, 5)
        Prod = Mid(UPCA, 7, 5)
        If Right(Mfg, 3) = "000" Or Right(Mfg, 3) = "100" Or Right(Mfg, 3) = "200" Then
            ValidDigits = Left(Mfg, 2) & Right(Prod, 3) & Mid(Mfg, 3, 1)
        ElseIf Right(Mfg, 2) = "00" Then
            ValidDigits = Left(Mfg, 3) & Right(Prod, 2) & "3"
        ElseIf Right(Mfg, 1) = "0" Then
            ValidDigits = Left(Mfg, 4) & Right(Prod, 1) & "4"
        Else
            ValidDigits = Left(Mfg, 5) & Right(Prod, 1)
        End If
        UPCA2UPCE = Left(UPCA, 1) & " " & ValidDigits & " " & Right(UPCA, 1)
    End If
End Function

 

[Rick Rothstein]

If we're converting UPC-A codes to UPC-E then more is required:

UPC Barcode Information and UPC A to UPC E Converter | TALtech

If the material at this first link is correct, and I have no reason to believe it isn't, then the OP must be doing some different kind of conversion because the link indicates that the OP's original sample (011000001126 with the spaces removed) would convert to 111120 which is not the 01111206 the OP said it should convert to. So we will definitely need clarification from the OP as to what he actually wants/needs.