VBA to pull a string of numbers between two specific numbers and place that in a label

  • I've written a formula that takes a scan from a barcode "010500022346936951723020110100043", in the next cell, it finds the digits between the "01" in the beginning and the "17" towards the middle. I would prefer to have a userform where TextBox10 is where the raw scan populates, and label 13's text is the string between the two numbers while omitting the 0 in the beginning if possible.

    Here's the formula that I used, utilizes 3 cells which is why it's so muddy: =IFERROR(MID(LEFT($AI14,IFERROR(FIND("17",$AI14),FIND("10",$AI14)-1)),FIND("01",$AI14)+2,LEN($AI14)),"")

    Then in the next cell, I use the Value function to remove the 0 from there.


    Is there a way to do this, pulling the same data from TextBox10, then omitting the 0 if it exists in the beginning of the new number and then names Label13?


    wow, that sounds confusing. I haven't tried anything in VBA yet, I haven't found anything that really fit what I was looking for.

  • Is there always 01 at the start, and is the first 17 always sure to be the one you want?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • In that case something like:


    Code
    1. Mid$(Left$(Textbox10.Text, Instr(Textbox10.text, "17")-1),3)


    should return the value you want.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why