# making a UPC zero suppressed

#### kenton71

##### New Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
This?

Excel 2010
AB
10 11000 00112 60 111120 6
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,1)&" "&SUBSTITUTE(SUBSTITUTE(MID(A1,3,12)," ",""),0,"")&0&" "&RIGHT(A1,1)

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

Last edited:
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

Last edited:
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.

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
ABCDEF
1creates array of all charcounts the zerosis it Zero SupressANSWER
20 11000 00112 606Zero Suppress0 111120 60 111120 6
30 11653 50112 502FALSEFALSEFALSE
Sheet40
Cell Formulas
RangeFormula
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.

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.

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

Use this UDF for UPC-A to E

Code:
``````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``````

Last edited:
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.

Replies
4
Views
93
Replies
5
Views
346
Replies
3
Views
311
Replies
2
Views
404
Replies
7
Views
486

1,207,421
Messages
6,078,436
Members
446,337
Latest member
nrijkers

### 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?

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