Extracting Barcode information from variable length barcode w/ alphanumeric characters

Hello All,

Please bear with me on my first question on this forum.

your assistance is greatly appreciated!

---

I assist in managing merchandise for a fashion photography studio. We track the received products against a document to confirm whether they will be part of a photography shoot.

I

• scan barcode into a worksheet (worksheet-SCAN / Column A).
• I want to strip the leading numbers and populate Column B (need help)
• Referencing Column B, split into its respective product-ID (6 digits), color code (3-alphanumeric characters), size-ID (3-alphanumeric characters), which are placed in their own columns (need some help)

Example barcodes:

1560000413551QN7S
36000394127QCR34B
1440000394029QB5S
7040000412543QF8S

My challenge is:

• Extracting the MAIN BARCODE (bold) by removing the leading numbers (italics), which are variable length (sometimes 3 or 4 zeros; (MAIN BARCODE always starts after a 0/zero))
• Size-ID is also a variable of length of character 1,2, or 3 characters - S or OS or 34B

Please see attached Workbook for the example.

Thank you in advance for your consideration!

So you need to delete everything up to the last zero.

To do that you must know how many zeros you have.

Lets say your number is in cell A1

1. So the number of Zeros is given by =LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))

So now we have to flag the last Zero so we can find it in the string

2. =Substitute(A1,"0","@",Pos) Where Pos is the number of Zero given by formula 1.

So =Substitute(A1,"0","@",LEN(A1)-LEN(SUBSTITUTE(A1,"0","")))

So now we need to Find the Position of the @ in our formula.

3. =FIND("@",SUBSTITUTE(A1,"0","@",LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))))

Finally we need to extract the Text to the right of the @

4. =MID(A1,FIND("@",SUBSTITUTE(A1,"0","@",LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))))+1,99)

Formula for B2 =MID(A2,FIND("@",SUBSTITUTE(A2,"0","@",MIN(LEN(A2)-LEN(SUBSTITUTE(A2,"0","")),4)))+1,99)


A15 and A16 end up with leading Zeros.
Formula for B15 =IF(LEFT(MID(A15,FIND("@",SUBSTITUTE(A15,"0","@",MIN(LEN(A15)-LEN(SUBSTITUTE(A15,"0","")),4)))+1,99),1)="0",MID(MID(A15,FIND("@",SUBSTITUTE(A15,"0","@",MIN(LEN(A2)-LEN(SUBSTITUTE(A15,"0","")),4)))+1,99),2,99),MID(A15,FIND("@",SUBSTITUTE(A15,"0","@",MIN(LEN(A15)-LEN(SUBSTITUTE(A15,"0","")),4)))+1,99))