formula to extract the first two characters after a space FROM THE RIGHT

[kbishop94]

the target string in cell A10 is:

1_6_YAM_Robert_Saltsman_300_KY_3:27

* where the "_" equals ONE space.

I've managed to get the formulas I need when starting over from the LEFT, but I can't get this figured out when needing to start from the RIGHT so that the formula returns:

"KY"


I need the formula to find the first space over from the right (the one after "KY" and before "3:27") and then grab the first TWO characters from that space and going towards the LEFT... it should return "KY"

Thanks

 

[Phuoc]

Try this

Book1
	A	B
1	1 6 YAM Robert Saltsman 300 KY 3:27	KY
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",500)),1000),500))

 

[kbishop94]

Try this

Book1
	A	B
1	1 6 YAM Robert Saltsman 300 KY 3:27	KY
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",500)),1000),500))

Excellent.

Thank you Phouc