returning specific text only

[Jane]

here is my text strong:
PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
101915744. MMU - DO NOT BREAKAP:

I want to return text starting with ALLOC but stop at and not include AP.

thank you in advance, jane

[Don Guillett]

use FIND or SEARCH to find the position of the desired text and then
incorporate into a MID function

--
Don Guillett
SalesAid Software
[email protected]
"Jane" <[email protected]> wrote in message
news:[email protected]...
> here is my text strong:
> PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> 101915744. MMU - DO NOT BREAKAP:
>
> I want to return text starting with ALLOC but stop at and not include AP.
>
> thank you in advance, jane

[Beege]

"Jane" <[email protected]> wrote in message
news:[email protected]...
> here is my text strong:
> PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> 101915744. MMU - DO NOT BREAKAP:
>
> I want to return text starting with ALLOC but stop at and not include AP.
>
> thank you in advance, jane

=MID(B2,SEARCH("ALLOC",B2,1),SEARCH("AP:",B2,1)-(SEARCH("ALLOC",B2,1)))

[CLR]

=(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))

and if you want the colon at the end.....

=(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))&":"

Vaya con Dios,
Chuck, CABGx3



"Jane" <[email protected]> wrote in message
news:[email protected]...
> here is my text strong:
> PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> 101915744. MMU - DO NOT BREAKAP:
>
> I want to return text starting with ALLOC but stop at and not include AP.
>
> thank you in advance, jane

[Jane]

purely as a time issue, ca you show me how that would look?


"Don Guillett" wrote:

> use FIND or SEARCH to find the position of the desired text and then
> incorporate into a MID function
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Jane" <[email protected]> wrote in message
> news:[email protected]...
> > here is my text strong:
> > PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> > 101915744. MMU - DO NOT BREAKAP:
> >
> > I want to return text starting with ALLOC but stop at and not include AP.
> >
> > thank you in advance, jane
>
>
>

[Don Guillett]

I see that you were fed the answer. I wonder if it will stick with you as
long as if you had done it yourself?

--
Don Guillett
SalesAid Software
[email protected]
"Jane" <[email protected]> wrote in message
news:[email protected]...
> purely as a time issue, ca you show me how that would look?
>
>
> "Don Guillett" wrote:
>
>> use FIND or SEARCH to find the position of the desired text and then
>> incorporate into a MID function
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Jane" <[email protected]> wrote in message
>> news:[email protected]...
>> > here is my text strong:
>> > PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
>> > 101915744. MMU - DO NOT BREAKAP:
>> >
>> > I want to return text starting with ALLOC but stop at and not include
>> > AP.
>> >
>> > thank you in advance, jane
>>
>>
>>

[Jane]

hmmm, tried both but got the #VALUE error. Is that a function of copying
from here to my spreadsheet? Do I need to type our the formula?

"CLR" wrote:

> =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))
>
> and if you want the colon at the end.....
>
> =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))&":"
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Jane" <[email protected]> wrote in message
> news:[email protected]...
> > here is my text strong:
> > PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> > 101915744. MMU - DO NOT BREAKAP:
> >
> > I want to return text starting with ALLOC but stop at and not include AP.
> >
> > thank you in advance, jane
>
>
>

[Beege]

"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
>I see that you were fed the answer. I wonder if it will stick with you as
>long as if you had done it yourself?
>
> --
> Don Guillett

Don,

Of course, your right. Teaching how to fish is better than giving fish away.
I'm here learning Excel, too, and I get excited when I can solve. It's also
very interesting to see how many different solutions and variations come up.
Moderation....

Beege

[Don Guillett]

I'm glad you agree. As time goes on you will find that there are usually
several ways to "skin the cat"

--
Don Guillett
SalesAid Software
[email protected]
"Beege" <[email protected]> wrote in message
news:[email protected]...
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
>>I see that you were fed the answer. I wonder if it will stick with you as
>>long as if you had done it yourself?
>>
>> --
>> Don Guillett
>
> Don,
>
> Of course, your right. Teaching how to fish is better than giving fish
> away. I'm here learning Excel, too, and I get excited when I can solve.
> It's also very interesting to see how many different solutions and
> variations come up. Moderation....
>
> Beege
>
>
>

[CLR]

Could be.........these are relatively long formulas and may "wrap" when
copying and pasting.....you may need to re-type if it don't all copy into one
cell.

Vaya con Dios,
Chuck, CABGx3



"Jane" wrote:

> hmmm, tried both but got the #VALUE error. Is that a function of copying
> from here to my spreadsheet? Do I need to type our the formula?
>
> "CLR" wrote:
>
> > =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))
> >
> > and if you want the colon at the end.....
> >
> > =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))&":"
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Jane" <[email protected]> wrote in message
> > news:[email protected]...
> > > here is my text strong:
> > > PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
> > > 101915744. MMU - DO NOT BREAKAP:
> > >
> > > I want to return text starting with ALLOC but stop at and not include AP.
> > >
> > > thank you in advance, jane
> >
> >
> >

[Don Guillett]

this may be of help

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
mstr = mstr & C
Next
Cells(x - 1, y) = mstr
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> Could be.........these are relatively long formulas and may "wrap" when
> copying and pasting.....you may need to re-type if it don't all copy into
> one
> cell.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Jane" wrote:
>
>> hmmm, tried both but got the #VALUE error. Is that a function of copying
>> from here to my spreadsheet? Do I need to type our the formula?
>>
>> "CLR" wrote:
>>
>> > =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))
>> >
>> > and if you want the colon at the end.....
>> >
>> > =(MID(A1,FIND("ALLOC",A1,1),FIND("AP:",A1,1)-FIND("ALLOC",A1,1)))&":"
>> >
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> > "Jane" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > here is my text strong:
>> > > PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
>> > > 101915744. MMU - DO NOT BREAKAP:
>> > >
>> > > I want to return text starting with ALLOC but stop at and not include
>> > > AP.
>> > >
>> > > thank you in advance, jane
>> >
>> >
>> >