Extract the characters between 2 spaces in a string

[Walter01]

Hi,

how to extract the characters between the 2nd and the 3rd space in a string? The number of these characters can vary.
For example: "spaces in a string" => returns "a".

In this thread there is a solution for the value between the 1st and the 2nd space:

Extract the value between 2 spaces in a string

Hi, I have some text in a single cell (example below) but want to extract the "-3000" value form it. So essentially the value/text between the 1st and 2nd space. Is there a simple method of doing this Pay -3000 house gas Thanks

www.mrexcel.com

=mid(a1,find(" ",a1)+1,find(" ",a1,find(" ",a1)+1)-find(" ",a1))
But I don't know how to find the position of the 3rd space.

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	North West Durham	Durham
3	Four Marks and Medstead	and
4	Torridge and West Devon	West
Data

Cell Formulas
Range	Formula
B2:B4	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[3]")

 

[Walter01]

How about

Fluff.xlsm
	A	B
1
2	North West Durham	Durham
3	Four Marks and Medstead	and
4	Torridge and West Devon	West
Data

Cell Formulas
Range	Formula
B2:B4	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[3]")

Thank you, but would it be possible to do it with simple excel commands like mid(), find(), etc.?

 

[Fluff]

Why? That just makes it harder & more messy.

=REPLACE(LEFT(A2&" ",FIND("^",SUBSTITUTE(A2&" "," ","^",3))-1),1,FIND("^",SUBSTITUTE(A2," ","^",2)),"")

 

[Walter01]

Why? That just makes it harder & more messy.

Thanks, I will use the 1st one. You are right, it is even more complicated.

 

[Fluff]

You're welcome & thanks for the feedback.