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