Barcode formula extract/formatting

[MattyFurTaTs]

my apologies but I can't find this in any other thread-. Perhaps I'm searching for the wrong words.

im dealing with barcodes that have these check digits. Scanner is picking up this barcode (01)4583967589324(17)130420(21)45443 without the parenthesis.

what formula can I use to say read the number between "01" and "17"?

can I put this formula into the same cell that I'm entering data in?

 

[chicagocomputerclasses]

Are the digits as characters always in the same position?

 

[MattyFurTaTs]

Are the digits as characters always in the same position?

a lot of times the number between the (10) and (17) are in the same amount of digits, but from time to time they are not.

the scanner reads this number (01)4583967589324(17)130420(21)45443 as 014583967589324171304202145443. So what I really need is some sort of formula to read between the 10 and the 17 and ignore the rest.

 

[MattyFurTaTs]

this is the barcode that's giving me trouble. All numbers appear in EXCEL as plain text without the parenthesis.

ex: 01102222223333341709123110A1345B21123

is there a formula that will remove "01" extract text "10222222333334", remove "17" extract text "091231, remove "10" extract text "A1345B", remove "21" extract text "1234"

I realize i'm asking for something so specific, but was hoping something was in inventory management and already figured this out.

any help, as always, GREATLY APPRECIATED

 

[chicagocomputerclasses]

well, if the amount of characters in between are not always the same it will be challenging.
the problem is if that 17, 10 or 21 appear somewhere within the code itself.

I think the characters are always the same amount in these barcodes for the reader, so if that's the case MID function will easily do this.

 

[chicagocomputerclasses]

in the event those numbers would never appear in the code itself you can use this logic to extract the data. The formula below will extract the first peace of number you need.
You can build on this logic and get the rest out of it.

=MID(A1,3,SEARCH("17",A1)-3)

 

[MattyFurTaTs]

CHICAGOCOMPUTERCLASSES - Thank you so much! I fiddled with this formula to make things work in many other different instances as well- I think i love you.

 

[chicagocomputerclasses]

Thank you for the kind words, but as I'm reading this I realize I must have been drunk
While the above formula will give you the accurate first instance, the second instance of between 2 numbers needs much complicated structure. Something like this:

=LEFT(MID(A1,SEARCH("17",A1)+2,LEN(A1)),FIND("21"-1,MID(A1,SEARCH("17",A1)+2,LEN(A1))))

 

[chicagocomputerclasses]

More precisely to get the string between 17 & 21 located in A1 cell you would use this

=LEFT(MID(A1,SEARCH("17",A1)+2,LEN(A1)),SEARCH("21",MID(A1,SEARCH("17",A1)+2,LEN(A1)))-1)

 

[DataWannaBe]

This is ages old, but I was just looking at the same problem and came across the fact that the GTIN14 barcode embedded within the GS1 barcode is always the 5th-18th character, so I just used:

=mid(A1,5,14)