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!