Merge 3 Formulas in 1 (Mid+len)

[vishan]

Hi Excel Gurus,

i have some text in Column BC1:1000, from which i want to separate that text by some conditions.

Here is sample of data: UnDelivered vide DRS #:DS/LDH/1314/000944 - 14 Aug 13 Reason : - CID - CONSIGNEE REQUESTED DELIVERY ON ANOTHER DATE and Available for DRS @ LDH

i want to separate it from ": -" to "and Available" like = "CONSIGNEE REQUESTED DELIVERY ON ANOTHER DATE"

i have 3 formulas for separating as follows mentioned but i cant make that in one.

1.BE2 = MID(BC2,FIND("CID -",BC2)+6,LEN(BC2))
2.BF2 = MID(BE2,FIND(" and Av",BE2),LEN(BE2))
3.BG2 = LEFT(BF2,LEN(BF2)-LEN(BF2))

pls help in merge this or suggest new formula.

thanks a lot in adv.

Regards
Vishan

 

[Jonmo1]

Try

=TRIM(MID(SUBSTITUTE(A1,"and Available",REPT(" ",LEN(A1))),FIND("CID -",A1)+6,LEN(A1)))

 

[markmzz]

Maybe this:

BE2-> =MID(A1,SEARCH("CID -",BC2)+6,SEARCH("and Available",BC2)-SEARCH("CID -",BC2)-7)

Markmzz