Hi Anil, You can use following two formula for your two columns respectively:-
=MID(B7,SEARCH("ORIG:",B7)+5,(SEARCH("ID:",B7)-1-SEARCH("ORIG:",B7)-5))
=MID(E7,SEARCH("BNF:",E7)+4,(SEARCH("ID:",E7)-1-SEARCH("BNF:",E7)-4))
Sample sheet is also attached for your better understanding.
Regards,
DILIPandey
On 10/15/11, Anil Bhange <anil.bha...@tatacommunications.com> wrote:
> Hi Ms-EXl-learner and Noorain,
>
> Could you help me with another formula for attached mail.
>
> Regards, Anil Bhange
> IP Phone - 512320 | Mobile - 90290 32123
>
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Anil Bhange
> Sent: Wednesday, October 12, 2011 04:14 PM
> To: excel-macros@googlegroups.com
> Subject: RE: $$Excel-Macros$$ Formula needed to extract the text from string
>
> Amazing... Ms-Exl-learner...
>
> This is exactly what I wanted... I was pretty sure this forum only can help
> me with solution...
>
> Thanks once again.. this saves my lot of time...
>
> Regards, Anil Bhange
> IP Phone - 512320 | Mobile - 90290 32123
>
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Ms-Exl-Learner .
> Sent: Wednesday, October 12, 2011 03:34 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Formula needed to extract the text from string
>
> Hi Anil,
>
> I Assume that your data is in Column A and your first row is having the
> column header.
>
> So your data will start from 2nd row of Column A (i.e. from A2 cell)
>
> A1 Data
> A2 /ENTRY-10 OCT TRF/REF 6004ABS68300004230 /ORD/ Q-FAST TELECOM BV
> STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 2011000000247 ACCOUNTNO
> 019481
> A3 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/201100000279 TRANSACTIEDATUM 10-10-2011
> A4 /ENTRY-10 OCTTRF/REF 6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/201100000295 TRANSACTIEDATUM 10-10-2011
>
> Copy and paste the below formula in any of the 2nd row cell (i.e. B2 cell)
> =TRIM(MID(SUBSTITUTE("/"&A2&REPT("/",6),"/",REPT(CHAR(32),255)),5*255,255))
>
> Drag the B2 cell formula below for the remaining cells of B column.
>
> Hope that helps!
>
> -----------------------
> Ms.Exl.Learner
> ----------------------
>
> On Wed, Oct 12, 2011 at 1:20 PM, Anil Bhange
> <anil.bha...@tatacommunications.com<mailto:anil.bha...@tatacommunications.com>>
> wrote:
> Hi Expert,
>
> I needed the formula which can extract the specific content from a Text,
> below is some sample excel cells
>
> To simplify this there is "/" sign after each content, and I need exact data
> which start after 4th "/" and till 5th "/" (which is mention below in Red).
>
> Can anybody tell me the formula.
>
>
>
> /ENTRY-10 OCT
> TRF/REF 6004ABS68300004230
> /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN /BNF/ INVOICENO 2011000000247 ACCOU
> NTNO 019481
>
> /ENTRY-10 OCT
> TRF/REF 6004ABS68300014073
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 201100000279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF 6004ABS68300025023
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 201100000295 TRANSACTIEDATUM 10-10-2011
>
>
>
>
>
>
>
> Anil Bhange
>
> Assistant Manager
> Financial Reporting & Compliance,
>
> TATA Communications Ltd.
> VSB, Fort, Mumbai - 400 001,
> India
>
>
>
> ' Desk : + 91 22 6659 2320
> | IP Phone : 51 2320 | Mobile :+ 91 90290 32123
>
> anil.bha...@tatacommunications.com<mailto:anil.bha...@tatacommunications.com>
>
>
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to
> excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to
> excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to
> excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Thanks & Regards,
DILIP KUMAR PANDEY, mvp
MBA,B.Com(Hons),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 62, India
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
picking up text.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
