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.

 

[MarcelBeug]

If you are looking for the part between the 2nd and 3rd space:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

 

[István Hirsch]

Is always the 3rd substring that you want to extract? If not, what is the rule?

 

[griffj]

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.

 

[griffj]

Is always the 3rd substring that you want to extract? If not, what is the rule?

There is no rule.

 

[István Hirsch]

There is no rule.

Is it the string which contains "ML" that you are searching for?

 

[griffj]

Is it the string which contains "ML" that you are searching for?

Yes, I want to extract 1MLGSTWIEL from 2PB IND3A 1MLGSTWIEL, but the position might change, the string/sub-string length might change.

 

[István Hirsch]

Yes, I want to extract 1MLGSTWIEL from 2PB IND3A 1MLGSTWIEL, but the position might change, the string/sub-string length might change.

Then try this:

=TRIM(MID(SUBSTITUTE(A1," ", REPT(" ",200)),FIND("ML",SUBSTITUTE(A1," ", REPT(" ",200)))-200,400))

 

[griffj]

Then try this:

=TRIM(MID(SUBSTITUTE(A1," ", REPT(" ",200)),FIND("ML",SUBSTITUTE(A1," ", REPT(" ",200)))-200,400))

That works, perfect, and is a little more refined than what I was just about to post as my answer!

=MID(A1,FIND(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",A1)+1)," ",""))))),A1),FIND(" ",A1,FIND("ML",A1))-FIND(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",A1)+1)," ",""))))),A1))

Simple when you know how! I will go an look at the REPT function.

 

[István Hirsch]

Then try this:

=TRIM(MID(SUBSTITUTE(A1," ", REPT(" ",200)),FIND("ML",SUBSTITUTE(A1," ", REPT(" ",200)))-200,400))

I see now that this formula does not work if the substring containing "ML" is in start position, so try this corrected version:

=TRIM(MID(SUBSTITUTE(" "&A1," ", REPT(" ",200)),FIND("ML",SUBSTITUTE(" "&A1," ", REPT(" ",200)))-200,400))