Need to extract specific data from barcode scans into spreadsheet

[MBP97]

I have 3 different types of barcode scans in a spreadsheet and need to extract specific characters from the scans for comparisons.
for example, one scan is --[)>+06:1J0000103189170605001:P1048059-00-E:Q6:K7700000393:5K1:4K00080:3QEA+#
I just need excel to return the P1048059-00-E for a comparison
but my other type of scan shows up as P76750 5RA1A
Just need to extract 76750 from it for comparison

Ideas?

[sandy666]

Two different sources and two different results.

--[)>+06:1J0000103189170605001:P1048059-00-E:Q6:K7700000393:5K1:4K00080:3QEA+#
P1048059-00-E

P76750 5RA1A
76750

IMHO you can try regular expression but maybe there is another way

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution. You refer to 3 types of data and showed only 2. What is the third one? ONE example is insufficient to see what pattersn there are in your real data. If all 3 sorts of codes can occur in a column of data, say so. If they are in different columns, say so and show them in spearate columns in your sample file.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

So... this works for your first one:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,":","",1),":",REPT(" ",100)),100,100))

IF & ONLY IF it was representative of your actual data (I'll bet that it wasn't representative...)

[MBP97]

adding some example scans of what we use under scans.xlsx

[Glenn Kennedy]

More examples of the one in row 7, please....

[Glenn Kennedy]

Do ALL of the shorter ones ALWAYS begin with P ???

[Glenn Kennedy]

This works for the shorter ones:
=IFERROR((MID(A5,2,SEARCH(" ",A5)-1)+0),TRIM(LOOKUP(99^99,--("0"&MID(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")),ROW(1:10000)))))+0)

For the longer one... are the number of characters before the target number always the same?

Does the target string ALWAYS occur between the first P and the first K? Is the target string always 12 characters long???

It'd also be helpful if you answered ALL of the questions from my previous post....

[AlKey]

Try this
Enter in B5 and copy down

Please Login or Register  to view this content.

[MBP97]

Yes, the short ones always begin with the letter "P"
And as far as I can get with my shipping department examples, all the long ones begin with the exact same character string before the number we need. We don't see a lot of the long numbers in our monthly shipping, so I'm having a hard time getting more examples.
And yes, it always occurs between the first "P" and "K" those are string markers for our customer, the number always is inside them.

[Glenn Kennedy]

OK. right, so.

This (monster) does the lot:

Please Login or Register  to view this content.

test thoroughly...

Incidentally- do check Alkey's offering out (I haven't...). He's the best on the Forum for this sort of Q.