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.

 

[Momentman]

Book1
	A	B
1	Tygoolhou3837T14	3837
2	Pjhdauajbsna25614D07	25614
Sheet15

Cell Formulas
Range	Formula
B1	{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0),MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(INDIRECT(MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0) & ":" & LEN(A1))),1)),0)-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Momentman]

If it always end with a string of 3 characters(like T14 and D07) then this should work

Book1
	A	B
1	Tygoolhou3837T14	3837
2	Pjhdauajbsna25614D07	25614
Sheet16

Cell Formulas
Range	Formula
B1	{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0),LEN(A1)-MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0)-2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sharky12345]

Perfect - thanks Momentman!

 

[Phuoc]

Or try:

=LOOKUP(10^35,--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),ROW(INDIRECT("1:"&LEN(A1)))))

Enter with C+S+E

 

[jtakw]

Hi,

Another way, Non-Array, Normally entered:

Book1
	A	B
1	Tygoolhou3837T14	3837
2	Pjhdauajbsna25614D07	25614
Sheet430

Cell Formulas
Range	Formula
B1	=MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),LEN(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99))-3)

 

[sharky12345]

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?

 

[jtakw]

That's Not what you originally asked, what you want now Requires VBA, so, as a matter of fact, it's completely the opposite.

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

One possible way is, within your Userform Code, Store/assign the Textbox value to a Cell in the sheet, then reference one of the above formulas of your choice to That cell...

 

[sharky12345]

Correct, it's not what I originally asked for - my original question was answered and I have now asked for assistance in taking it a step further, as I then went on to say:

I have 1 further question now

As the project has now developed further I have realised that VBA may be a better solution, which is why I am now asking if the solution can be adapted to use in VBA.

 

[jtakw]

I've edited my post above to include a suggestion.

Your project has changed quite quickly, within 2 1/2 hours...