Pick up some text in a cell

[Elton Law]

Dear Expert,

Want to extract 3 letters after /, in this case, the answer should be JPY.

EUR/JPY SHORT EUR 20 MIO
AUD/JPY SHORT AUD 1 MIO
USD/JPY SHORT USD 1 MIO
Can ?
Thanks
Elton

 

[David Biddulph]

=MID(A2,FIND("/",A2)+1,3)

 

[Elton Law]

Hi David,
If this time, I want to capture the 3 letters in front of mio please ?'
EUR/JPY SHORT EUR 20 MIO
AUD/JPY SHORT AUD 1 MIO
USD/JPY SHORT USD 1 MIO
In this case, it should be 20, 1, 1 in each each.
Thanks
Elton

 

[Ashish Mathur]

Hi,

Try this

=1*MID(C11,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C11&"0123456789")),SEARCH("
",C11,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C11&"0123456789")))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C11&"0123456789")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Teethless mama]

Try this:
=--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),277,99))

copy down

 

[Teethless mama]

Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

 

[Glenn]

=MID(A1,FIND("MIO",A1)-3,3)

Hi David,
If this time, I want to capture the 3 letters in front of mio please ?'
EUR/JPY SHORT EUR 20 MIO
AUD/JPY SHORT AUD 1 MIO
USD/JPY SHORT USD 1 MIO
In this case, it should be 20, 1, 1 in each each.
Thanks
Elton

 

[Elton Law]

tks all ... all are super stars !

Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

 

[Teethless mama]

You're Welcome!

tks all ... all are super stars !

 

[Glenn]

It doesn't "capture the 3 letters in front of mio"?

(Where I took "letters" to mean "characters")