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 - Слава Україні
  • 21,717
MNchica99
  • 23
  • 4

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
  • 176
  • 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 '18 at 16:44
  • Will this question help? https://superuser.com/questions/483419/how-to-split-a-string-based-on-in-ms-excel – JoeJam Apr 18 '18 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 '18 at 18:27
  • Look at the what? – JoeJam Apr 18 '18 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 '18 at 18:33
  • Because the project and sample number is always a string of 12 characters, I was trying to write a formula to look at the 11th character. If it was a "0", then display the 12th character as the first sample number, but if the 11th character was not a "0" display characters 11 and 12. But trying to combine that with the IF(SEARCH( function to look for the colon in the formula left me with a mess that didn't provide my desired outcome. – MNchica99 Apr 18 '18 at 18:35
  • Try this: =IFERROR((MID(A1,FIND(":",A1)+1,FIND("-",A1)-FIND(":",A1)-1)),) This will return the charachters after the semicolon and before the dash as well as return 0 if there is no sample number. – JoeJam Apr 18 '18 at 18:43
  • Numbers after the colon would be the second number in the sample range. In this example, D00012345006, "D00012345" is the project and "006" means sample 6. If it said D00012345006:10, it would mean project D00012345, samples 6:10. So if there is a colon, I need the significant numbers before the colon, (but excluding the 1 or 2 leading 0's) as well as the colon and numbers after the colon. – MNchica99 Apr 18 '18 at 18:50
  • How characters are the sample numbers? Are they always less than 10? If yes then the following will work: =IFERROR((MID(A1,FIND(":",A1)-1,FIND("-",A1)-FIND(":",A1)+1)),MID(A1,FIND("-",A1)-1,1)) – JoeJam Apr 18 '18 at 18:57
  • That is close! The first sample number could be 1 or 2 digits, so the formula breaks if the sample number before the colon is 2 digits. – MNchica99 Apr 18 '18 at 19:15
  • 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 Apr 18 '18 at 19:24