Extract text between second and first space from the right in text string

[CaptainGravyBum]

Book1
	A
1	SG EQUIPMENT FINAN A096031949 DDR
2	DE LAGE LANDEN LEA 24350098914 DDR
Sheet1

Hello,
I need to extract the reference number from the text strings above, I have many rows of data that my formula needs to work on, but these two should serve as suitable examples.
The reference always sits after the second space from the right and I don't need any of the information after the first space from the right. The number isn't always the same length and the number of spaces before the reference number do change as you can see in the example, so it strongly relies on a search for the number of spaces from the right which remain the same.
I've tried various MID, LEFT, RIGHT, SEARCH combinations, but I can't quite get it to consider the variable lengths of data.

I appreciate any help you can give.

 

[Fluff]

How about

=TEXTBEFORE(TEXTAFTER(A1," ",-2)," ")

 

[CaptainGravyBum]

How about

=TEXTBEFORE(TEXTAFTER(A1," ",-2)," ")

Wow!
I'd tried a very similar formula earlier and it gave me nothing useful, literally one character different than yours.
Thanks so much @Fluff, this worked perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CaptainGravyBum]

You're welcome & thanks for the feedback.

I've applied the formula to my data and discovered that not all references follow the same criteria. Do you have something I could use that just looks for numbers in the string and returns the data from that?
The number will always have a space after it so ideally it would be something along the lines of finding the space after the number and returning all data preceding the space until it hits another space, purely because the number sometimes has a letter preceding it.
Sorry if this sounds like nonsense.

 

[Fluff]

Can you supply some sample data.

the number sometimes has a letter preceding it

That means it is not a number.

 

[CaptainGravyBum]

Ah, fair enough.
The original sample data will work, but I've provided a few more here so you can see where the rule of picking up data from the second space from the right is failing (row 2).

Book2
	A
1	ALDERMORE BANK PLC C0015954106168976 DDR
2	DE LAGE LANDEN LEA 24350418751 FIRST DDR PAYMENT DDR
3	DE LAGE LANDEN LEA 24350418751 DDR
Sheet1

 

[Cubist]

Maybe this. This looks for the last numeric position in the string and extracts the chunk of text with the last numeric character.

Book1
	A	B
1	ALDERMORE BANK PLC C0015954106168976 DDR	C0015954106168976
2	DE LAGE LANDEN LEA 24350418751 FIRST DDR PAYMENT DDR	24350418751
3	DE LAGE LANDEN LEA 24350418751 DDR	24350418751
4	SG EQUIPMENT FINAN A096031949 DDR	A096031949
5	DE LAGE LANDEN LEA 24350098914 DDR	24350098914
Sheet12

Cell Formulas
Range	Formula
B1:B5	B1	=LET(x,XMATCH(TRUE,ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1)),0,-1), TEXTAFTER(LEFT(A1,x)," ",-1))

 

[CaptainGravyBum]

Hi @Cubist ,
This works perfectly, looks quite complicated to me so I appreciate your swift solution.
Thanks.

 

[Cubist]

You're welcome.