Remember Me?

#### Menu

#1
April 27th 09, 11:46 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2009 Posts: 173
Pick up some text in a cell

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

#2
April 27th 09, 12:08 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
Pick up some text in a cell

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

"Elton Law" wrote in message
news
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

#3
April 27th 09, 12:36 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2009 Posts: 173
Pick up some text in a cell

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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news
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

#4
April 27th 09, 12:54 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 1,766
Pick up some text in a cell

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Elton Law" wrote in message
...
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news
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

#5
April 27th 09, 02:19 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,718
Pick up some text in a cell

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

copy down

"Elton Law" wrote:

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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news
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

#6
April 27th 09, 03:02 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,718
Pick up some text in a cell

Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

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

"Glenn" wrote:

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

Elton Law wrote:
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news 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

#7
April 27th 09, 03:30 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
Pick up some text in a cell

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

Elton Law wrote:
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news
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

#8
April 27th 09, 04:02 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2009 Posts: 173
Pick up some text in a cell

tks all ... all are super stars !

"Teethless mama" wrote:

Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

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

"Glenn" wrote:

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

Elton Law wrote:
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news 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

#9
April 27th 09, 04:25 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,718
Pick up some text in a cell

You're Welcome!

"Elton Law" wrote:

tks all ... all are super stars !

"Teethless mama" wrote:

Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

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

"Glenn" wrote:

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

Elton Law wrote:
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news 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

#10
April 27th 09, 05:02 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
Pick up some text in a cell

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

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

Teethless mama wrote:
Your formula returns incorrect value with this example

EUR/JPY SHORT EUR 20000 MIO

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

"Glenn" wrote:

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

Elton Law wrote:
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

"David Biddulph" wrote:

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

"Elton Law" wrote in message
news 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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Katie Excel Discussion (Misc queries) 5 November 20th 08 02:57 PM Peanut Excel Worksheet Functions 7 March 21st 07 10:08 PM David Excel Worksheet Functions 2 December 13th 06 10:05 AM PJH Excel Discussion (Misc queries) 2 March 30th 06 05:11 AM Pegita Excel Worksheet Functions 1 June 23rd 05 07:24 PM

All times are GMT +1. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017