Extract numbers only from middle of string

[sharky12345]

Is there a formula I can use which will extract the numbers only from the middle of a string?

The string currently looks like this;

Tygoolhou3837T14

I need to be able to extract the '3837' part - this will always be in between 2 letters. The number of letters before may change, but the number after will not, so another example would be;

Pjhdauajbsna25614D07

If it's not possible using a formula then I'm equally to use VBA but I would prefer a formula.

 

[sharky12345]

Yes, it has - though don't know why that is an issue!

 

[jtakw]

It is Not an issue per se, had it been a request for a VBA solution, I would not have posted a formula, I would not have posted to this thread at all.

I'm sure someone would be able to help.
Have a nice day!

 

[Momentman]

Guys, thanks to all of you for your suggestions, I have 1 further question now...

How would I use any of those formulas to achieve the same result on a userform, using a textbox value as the value of 'A1'?

I assume a worksheetfunction but I don't know how to do that if someone can assist me?

One way is this

Function ExtFunc(Str As String)
    Dim I As Long, Pos As Long
    If Len(Str) <> 0 Then
        For I = 1 To Len(Str)
            If IsNumeric(Mid(Str, I, 1)) Then
                Pos = I
                Exit For
            End If
        Next I
    End If
    ExtFunc = Mid(Str, Pos, Len(Str) - Pos - 2)
End Function

The UDF above will do the same thing as the formula ---at least for your problem as described

So if you wanted to put in Textbox2 the value from extracting the numbers from textbox1, you could do something like

TextBox2.Value =ExtFunc(TextBox1.Value)

where ExtFunc calls the function used for the extraction