2

I manage a workbook where we are continually adding rows as products are scheduled. The scheduler would like to start using a barcode scanner to input some of the data, to reduce transcription errors. The existing barcode inputs data that looks like these examples below.

D00030827001-800649.3
D00030850013:20-800649.3
D00030850001:2-800649.4

The first 9 characters are the project number (D00030850). Next three characters are the first sample number (013). If there is a range of samples, there will be a colon and the last sample in the range (20). After the dash is the product number (800649.3). I'm trying to piece this apart into two columns, one for the project/sample number and one for the product number, which would look like this:

D30827-1       800649.3
D30850-13:20   800649.3
D30850-1:2     800649.4

I know how to get the product number into its own column (=MID(A1,FIND("-",A1)+1,LEN(A1))).  I'm struggling with how to get the project/sample number(s) in the first column, and formatted as displayed above. Essentially, I need the first character, skip the next three 0s, then the next 5 digits, then a "-", then the significant digit(s) before the dash or colon. If there is a colon I need the colon and the digits after the colon. Any help would be appreciated!

Scott - Слава Україні
  • 22,532
MNchica99
  • 23

1 Answers1

0

I was able to do it in a 2 step process.

1) =IFERROR((MID(A1,FIND(":",A1)-2,FIND("-",A1)-FIND(":",A1)+2)),MID(A1,FIND("-",A1)-2,2)) returns the product and sample number(if applicable).

2) Parse the zeros from step 1 results =IF(LEFT(B1,1)="0", MID(B1,FIND("0",B1)+1,LEN(B1)),B1) .

JoeJam
  • 180