Extract variable length string between first and last spaces

[ExceL0ver]

Hi all,

I have a column of strings of variable lengths that contain numbers and words, with spaces in between the words and numbers. Some strings contain several spaces in a row, sandwiching the part I wish to extract. I would like to extract all strings so that all characters before and including the first space are removed, and the last space and all characters after it are removed e.g.,
"4hg2 eljdg lsigh lrbdsd 3403" should be extracted as "eljdg lsigh lrbdsd"
"024nk ketnc osen enlakb jkasdl 34nh3" should be extracted as "ketnc osen enlakb jkasdl"

I've managed to remove the first part with the formula =TRIM(MID(A2,FIND(" ",A2)+1,300)) and tried to amend this formula to also remove the last part, without success. Can anyone suggest a formula that would work, please?

 

[Georgiboy]

Maybe:

Book1
	A	B
1	4hg2 eljdg lsigh lrbdsd 3403	eljdg lsigh lrbdsd
2	024nk ketnc osen enlakb jkasdl 34nh3	ketnc osen enlakb jkasdl
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=TEXTJOIN(" ",TRUE,DROP(DROP(TEXTSPLIT(TRIM(A1)," "),,-1),,1))

 

[RoryA]

You could also use

=TEXTBEFORE(TEXTAFTER(A2," ",1)," ",-1)

 

[ExceL0ver]

Thank you both Georgiboy and RoryA, both solutions work perfectly!

 

[Georgiboy]

You could also use

=TEXTBEFORE(TEXTAFTER(A2," ",1)," ",-1)

Keep forgetting about that part of the text functions, get carried away with all of the new functions and use too many.

Instead I went for the double drop...

 

[RoryA]

get carried away with all of the new functions and use too many

Me too - nothing wrong with a bit of overkill every now and then though!

 

[ExceL0ver]

Glad that you did actually, I'd never seen the DROP function used before so I've also learnt another thing, thanks!