Trim Barcode

SKETCHwade · 07-11-2019 02:28 PM

Hi Everyone,
I am trying to remove excess information from barcodes. Scanning a couple of items has came back with information like this:

CATAA( – 6I0273
CATAA --3261643
CATAA: – 1R0778
CATAAPH–3375270

So “CATAA” is a constant among them, “(” or “PH” is the “origin” of the product.
The numbers on the right are the actual part number that I care about.

I would like to create an expression that fills in [Part Number] based off of [Barcode]
I’ve looked into SPLIT and TRIM, but because the “Origin” is very often different im unsure how to consistently remove everything left of the part number.

Thanks

Mike_A

Is the part you are interested in always delimited by a “-“? If yes… you can try a combination of Find() and Right() to leave you with the part you want. See some information here:

SKETCHwade

Yeah I found something very similar in appsheets examples:

Column AppName with a value of “Sales-10305”

LEFT([AppName], FIND("-", [AppName]))** returns “Sales”

This works for the most part for me. My current formula is:

IFS(Contains([Barcode],“CAT”),
RIGHT([Barcode], FIND("–",SUBSTITUTE([Barcode], " ", “”))),

Contains([Barcode],“742330”),
“P5”,

AND(Contains([Barcode],“P”),Contains([Barcode],"-"),LEN([Barcode]) = 15),
LEFT([Barcode], FIND("-", [Barcode]) - 1)
)

With the CAT section I am sometimes left with a space on the left of the part number.

Any Ideas?

Steve

Wrap your entire expression in TRIM().

SKETCHwade

Well I guess it’s just that easy!

Thank you! @Steve