subject:"$$Excel\-Macros$$ Formula needed to extract the text from string"

RE: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-16 Thread Dilip Pandey

You are welcome.

Regards,
DILIPandey
On 16 Oct 2011 14:16, "Anil Bhange" 
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  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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV
> > STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247
> ACCOUNTNO
> >  019481
> > A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY
> > OUDAEN
> > 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
> > A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY
> > OUDAEN
> > 26 LELYSTAD /BNF/20110295 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
> > 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  6004ABS6834230
> > /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM DE MEERN /BNF/
> > INVOICENO 201100247 ACCOU
> > NTNO019481
> >
> > /ENTRY-10 OCT
> > TRF/REF  6004ABS68300014073
> > /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> > 20110279 TRANSACTIEDATUM 10-10-2011
> >
> > /ENTRY-10 OCT
> > TRF/REF  6004ABS68300025023
> > /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> > 20110295 TRANSACTIEDATUM 10-10-2011
> >
> >
> >
> >
> >
> >
> >
> > Anil Bhange
> >
> > Assistant Manager
> > Financial Reporting & Compliance,
> >
> > TATA Communications Ltd.
> > VSB, Fort,  Mumbai - 400 001,
&g

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-15 Thread Dilip Pandey

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  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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV
> STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO
>  019481
> A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
> A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/20110295 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
> 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  6004ABS6834230
> /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN /BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 20110295 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
> --
> --

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-15 Thread ashish koul

MID(E7,FIND("BNF:",E7)+4,FIND("ID:",E7)-FIND("BNF:",E7)-4)

On Sat, Oct 15, 2011 at 5:22 PM, 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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV
> STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247
> ACCOUNTNO019481
>
> A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY
> OUDAEN 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
>
> A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/20110295 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> 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  6004ABS6834230
> /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN */BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/
> 20110295 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
>
>  
>
>  
>
> --
>
> --
> 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
>
> <><><><><&g

RE: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-15 Thread Anil Bhange

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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV 
STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO  
  019481
A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 
LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN 26 
LELYSTAD /BNF/20110295 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 
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  6004ABS6834230
/ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
DE MEERN /BNF/ INVOICENO 201100247 ACCOU
NTNO019481

/ENTRY-10 OCT
TRF/REF  6004ABS68300014073
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110279 TRANSACTIEDATUM 10-10-2011

/ENTRY-10 OCT
TRF/REF  6004ABS68300025023
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110295 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/

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Dilip Pandey

Hi Anil,

Designed a formula using Substitute function.  See the attachment and
let me know if this helps.

Regards,
DILIPandey

On 10/12/11, Anil Bhange  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  6004ABS6834230
> /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN /BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
> 20110295 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
>
>
>
> --
> --
> 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


Extract text by DILIPandey.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Haseeb Avarakkan

Hello Anil,

A short version to Ms.Exl.Learner

=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",255)),4*255,255))

HTH
Haseeb

For help & tips visit;
http://www.excelfox.com/forum/forum.php

-- 
--
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

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Ms-Exl-Learner .

Hi Anil,

Thanks for your reply and you are welcome!

But I am a part time visitor of this group and you can find my replies only
for the questions which is interesting to work with it.

>From my point of view many of the extra-ordinary excel forums / discussion
groups are available and this is one of it, since the Legendary Excel
MVP Don Guillett also getting joined in this group.

---
Ms.Exl.Learner
--

On Wed, Oct 12, 2011 at 4:13 PM, Anil Bhange <
anil.bha...@tatacommunications.com> wrote:

> 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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV
> STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247
> ACCOUNTNO019481
>
> A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY
> OUDAEN 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
>
> A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN
> 26 LELYSTAD /BNF/20110295 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> 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  6004ABS6834230
> /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN */BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/
> 20110295 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
>
>  
>
>  
>
> --
>
> --
> 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*

RE: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Anil Bhange

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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV 
STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO  
  019481
A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 
LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN 26 
LELYSTAD /BNF/20110295 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 
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  6004ABS6834230
/ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
DE MEERN /BNF/ INVOICENO 201100247 ACCOU
NTNO019481

/ENTRY-10 OCT
TRF/REF  6004ABS68300014073
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110279 TRANSACTIEDATUM 10-10-2011

/ENTRY-10 OCT
TRF/REF  6004ABS68300025023
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110295 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

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread NOORAIN ANSARI

Dear Anil,

See attached sheet if it help to u..

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Wed, Oct 12, 2011 at 1:20 PM, Anil Bhange <
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  6004ABS6834230
> /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN */BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/
> 20110295 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
>
> ** **
>
> ** **
>
> --
>
> --
> 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
>

-- 
--
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


See it if help.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Ms-Exl-Learner .

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  6004ABS6834230 /ORD/ Q-FAST TELECOM BV
STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247
ACCOUNTNO
019481
A3 /ENTRY-10 OCT TRF/REF  6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN
26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011
A4 /ENTRY-10 OCTTRF/REF  6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN
26 LELYSTAD /BNF/20110295 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> 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  6004ABS6834230
> /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
> DE MEERN */BNF/ INVOICENO 201100247 ACCOU
> NTNO019481
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300014073
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/
> 20110279 TRANSACTIEDATUM 10-10-2011
>
> /ENTRY-10 OCT
> TRF/REF  6004ABS68300025023
> /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/
> 20110295 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
>
> ** **
>
> ** **
>
> --
>
> --
> 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

-- 
--
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

$$Excel-Macros$$ Formula needed to extract the text from string

2011-10-12 Thread Anil Bhange

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  6004ABS6834230
/ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM
DE MEERN /BNF/ INVOICENO 201100247 ACCOU
NTNO019481

/ENTRY-10 OCT
TRF/REF  6004ABS68300014073
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110279 TRANSACTIEDATUM 10-10-2011

/ENTRY-10 OCT
TRF/REF  6004ABS68300025023
/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/
20110295 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



-- 
--
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

RE: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

RE: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

RE: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

Re: $$Excel-Macros$$ Formula needed to extract the text from string

$$Excel-Macros$$ Formula needed to extract the text from string

11 matches

Site Navigation

Mail list logo

Footer information