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!

Improve this question

1 Answer 1

Reset to default
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) .

Improve this answer
11
  • I thought of this, too, but since they are continually adding rows to the spreadsheet, they would need to perform this function every time they added data. I guess I could write a macro to perform this function every time they add rows or save the spreadsheet, I was just hoping for a formula I could copied down one of the columns so no extra steps would be done after they added a row.
    – MNchica99
    Apr 18, 2018 at 16:44
  • Will this question help? superuser.com/questions/483419/…
    – JoeJam
    Apr 18, 2018 at 17:19
  • I read through that question a few times trying to apply it to my situation, and I'm not seeing it. The problem for me is the fact that there may or may not be a colon designating multiple samples. And if there is multiple samples, I need to display the range. I tried to write a formula that would look at the
    – MNchica99
    Apr 18, 2018 at 18:27
  • Look at the what?
    – JoeJam
    Apr 18, 2018 at 18:31
  • Sorry, hit enter too soon. I've been able to write a long nasty formula that gets me most of the way there, my biggest problem is trying to extract what the first number in the range is. Because the sample number part of the string is always a three-digit number, counting up from 001, I may just want to display the "1", but if it starts at double-digits, 011, I need to display "11".
    – MNchica99
    Apr 18, 2018 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.