LEFT, RIGHT and FIND

[msixdatateam]

Hi

I'm trying to extract some data from a string. An example of the string is: ABBN Aug14 12000 P.

The third term (12000) can be anywhere between 1 and 5 figures long. The last figure (P) will either be P or C.

I'm trying to extract purely the third term, despite its length. This seems to work but only when the third term is either 4 or 5 figures long:

=IFERROR(IF(FIND(" ",LEFT(RIGHT(A2,7),5),1)=1,RIGHT(LEFT(RIGHT(A2,7),5),4),LEFT(RIGHT(A2,7),5)),LEFT(RIGHT(A2,7),5))

Any ideas?!

Thanks

[AlKey]

Try this formula

in B1

it will work on any length to extract the third group of characters.

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A:$C)-1)*255+1,255))

Or just this

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),2*255,255))

	A	B
1	ABBN Aug14 12000 P.	12000