Find string between two spaces

[griffj]

Hi,

I am trying to find a sub-string between two spaces in a string.

Example String;
2PB IND3A 1MLGSTWIEL ANO

I would like to extract 1MLGSTWIEL

So far I have this formula...

=IF(ISERR(FIND("ML",A1)),"",RIGHT(LEFT(A1,FIND("ML",A1)+1),LEN(LEFT(A1,FIND("ML",A1)+1))-FIND("~", SUBSTITUTE(LEFT(A1,FIND("ML",A1)+1)," ","~",LEN(LEFT(A1,FIND("ML",A1)+1))-LEN(SUBSTITUTE(LEFT(A1,FIND("ML",[@Description])+1)," ",""))))))

which finds the 'ML' part of the string and then returns everything up to the previous space, but past the 'L' I can't seem to get it to work.

The positions will not always be the same.

Any suggestions would be great.

 

[Rick Rothstein]

That works a treat...but, it is not always the 2nd and 3rd space. This is a variable that could be different on each line.

Just checking to be sure... is it by any chance always the next to last delimited item in the text? If it is, you could use this formula...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),400),200))