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.
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:
I know how to get the product number into it's 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!