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

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

--
Don Guillett
SalesAid Software

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

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

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

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

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

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

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

"Beege" wrote in message
...
"Don Guillett" wrote in message
...
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

"Jane" wrote in message
...
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)))

=(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" wrote in message
...
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

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

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

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

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