Forum Discussion

Deleted's avatar
Deleted
May 08, 2020

Barcode Scanner data entry

Good afternoon,

I am attempting to use a Zebra bar-code scanner to use for data entry. I need to separate and remove some information that the scanner adds into the information.

 

the tag will scan as

240P0789AMXT-NC02103592127

I need to separate into 2 cells P0789AMXT-NC02 3592127 

 

I know the bar-code will always scan with the 240 in the 1st part of the data and to separate the other data at the end it will use 10. 

 

I was using =MID(A2,FIND("P",A2,1),FIND(10,A2)-FIND("P"A2,)) for the 1st portion of data and =RIGHT(A2,7) for the second part.

 

but this doesn't seem to work after I save the spreadsheet, close and reopen it. I have attempted to use the flash fill option and that doesn't work every time or doesn't recognize the pattern, I will have many combinations in the first portion of data, it won't always be the same length or have the same combination of numbers and letters.

 

I am at a loss... any help is appreciated.

 

excel
Formulas and Functions

6 Replies

  • wsantos's avatar
    wsantos
    Brass Contributor
    May 08, 2020

    Deleted =MID(A3,4,FIND("10",A3,FIND("-", A3))-4)

    this assumes the P is always at the same position.

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 08, 2020

    maybe it was a cut and paste error but this formula isn't correct:
    =MID(A2,FIND("P",A2,1),FIND(10,A2)-FIND("P"A2,))
    maybe try:
    =MID(A2,FIND("P",A2,1),FIND("10",A2)-FIND("P",A2,1))

     

    btw, if you know it will always scan in starting as 240P then why not replace all the FIND("P"... with a fix number?

      • mtarler's avatar
        mtarler
        Silver Contributor
        May 08, 2020

        oh wait, if you know that the part after the "10" is 7 digits long (based on the 2nd formula) then you can use:
        =iferror(MID(A2,FIND("P",B2,1),LEN(B2)-8-FIND("P",B2,1)),"")
        that said I see line 14 doesn't have a "P". was that a mistake or another problem to deal with?

         

        and if you can't assume "10" is followed by 7 digits then you can use:

        =IFERROR(MID(B2,FIND("P",B2,1),FIND("10",B2,FIND("-",B2))-FIND("P",B2,1)),"")

    • Deleted's avatar
      Deleted
      May 08, 2020

      mtarler 

       

      Hi Matt,

      That worked partially, but it is still not giving the response that I am looking for. I have attached the spreadsheet with test data. I am not sure if I can do fixed number because the information varies so much. Do you have any thoughts on improving? 

Resources