How To Extract Certain substring within a cell of Strings

  • This is what is in my cell A5:


    "Model:55555 blue toy car"


    I want to extract '55555' from the whole string and display it into a different cell (D4).
    I have:
    Start = InStr("Model:", A5) + 6
    Range("D4").Value = Mid(A5, InStr("Model:", A5) + 6, 5)


    When I run this, nothing shows up i cell D4.


    To debug it, I threw in: "Range("C2").Value = Start"
    and C2 displays a 7 so I know the first line of code works. I looked up the Mid function and the format seems to be Mid("string", n, length). I can't put in
    "Model:55555 blue toy car" as the string because the model number will change so I want a more general code. Is there a different function besides MID that will work better?

  • Re: How To Extract Certain substring within a cell of Strings


    If it doesn't have to be VBA this formula works on the example.


    Assuming string is in A1.


    Code
    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",256),1),":",REPT(" ",256)),256,256))
  • Re: How To Extract Certain substring within a cell of Strings


    Thanks for your help! unfortunately it has to be through vba because I need to make a more generalized code that works for many different types of data, I just submitted like this to narrow down the problem at hand. Also your Trim command displays "NOSE" on excel.

  • Re: How To Extract Certain substring within a cell of Strings


    With "Model:55555 blue toy car" in A5 and using the UDF like this:

    Code
    =GetModel(A5)


    You should get the desired results.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!