You are welcome. Regards, DILIPandey On 16 Oct 2011 14:16, "Anil Bhange" <anil.bha...@tatacommunications.com> wrote:
> Amazing... I know this group can help me... it save my lot of time... > > Thanks Dilip & Ashish > > Regards, Anil Bhange > IP Phone - 512320 | Mobile - 90290 32123 > > -----Original Message----- > From: Dilip Pandey [mailto:dilipan...@gmail.com] > Sent: Saturday, October 15, 2011 09:59 PM > To: excel-macros@googlegroups.com > Cc: Noor Ali Sayyed; Anil Bhange > Subject: Re: $$Excel-Macros$$ Formula needed to extract the text from > string > > 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.bhange@tatacommunicati > > ons.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.bhange@tatacommunicatio > > ns.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
