returning specific text only

J

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
 
D

Don Guillett

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

Beege

Jane said:
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
Click to expand...

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

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
 
D

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

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

Beege

Don Guillett said:
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?
Click to expand...

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
 
D

Don Guillett

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

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
 
D

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
 
Top