How To Extract Certain substring within a cell of Strings

ohyaitsysh · Jun 2nd 2016

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?

FlameRetired · Jun 2nd 2016

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))

ohyaitsysh · Jun 2nd 2016

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.

skywriter · Jun 2nd 2016

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.

Code

Function GetModel(cell As Range)
If cell.Cells.Count > 1 Then
    GetModel = "Pick only 1 cell."
    Exit Function
End If
Dim regex As Object
Dim m As Object
Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "Model:(\d+)"
    If .test(cell) Then
        Set m = .Execute(cell)
        GetModel = m.Item(0).submatches(0)
    Else
        GetModel = "No model number found."
    End If
End With


End Function

Display More

How To Extract Certain substring within a cell of Strings

Participate now!

Share