SOLVED

Remove text between two characters multiple times

%3CLINGO-SUB%20id%3D%22lingo-sub-187820%22%20slang%3D%22en-US%22%3ERemove%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187820%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20that%20has%20a%20text%20string%20with%20various%20lengths.%20What%20I'm%20trying%20to%20do%20is%20remove%20text%20that%20starts%20with%20%22%7C%22%20and%20ends%20with%20%22%3B%22.%20The%20text%20between%20those%20two%20characters%20is%20always%20going%20to%20be%2036%20characters.%20There%20can%20be%20multiple%20occurrences%20and%20what%20is%20between%20the%20two%20characters%20will%20vary%20from%20row%20to%20row.%20I%20cannot%20use%20VB%20only%20a%20formula.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EHere%20is%20my%20text%20string%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnnouncements%7C95029fcd-6b68-45bf-9f80-a2b2d90540f3%3BPersonal%20Information%7C8096f02e-25e3-4416-8dbb-b2a58d309d4e%3BPersonnel%20Statistical%20Reporting%7C9f1a73f0-5ce6-4abb-9fe1-44ab59350708%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20do%20is%20remove%20from%20the%20string%20what%20is%20between%20%22%7C%22%20and%20%22%3B%22%20so%20my%20new%20text%20string%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnnouncements%2C%20Personal%20Information%2C%20Personnel%20Statistical%20Reporting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20formula%20below%20I%20get%20this.%20Only%20one%20instance%20is%20removed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnnouncements%2C%20Personal%20Information%7C8096f02e-25e3-4416-8dbb-b2a58d309d4e%3BPersonnel%20Statistical%20Reporting%7C9f1a73f0-5ce6-4abb-9fe1-44ab59350708%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3DIFERROR(SUBSTITUTE(A1%2CMID(LEFT(A1%2CFIND(%22%3B%22%2CA1))%2CFIND(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CA1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-187820%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187968%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187968%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked...Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187862%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187862%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Chris%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20very%20long%20formula%3A%3C%2FP%3E%3CPRE%3E%3DSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2C%22%2C%20%22%2C%22%22%2C(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2C%22%2C%20%22))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CMID(LEFT(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)%2CSEARCH(%22%3B%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2CSEARCH(%22%7C%22%2CSUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22))%2CLEN(SUBSTITUTE(A1%2CMID(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CSEARCH(%22%7C%22%2CA1)%2CLEN(A1))%2C%22%2C%20%22)))%2C%22%2C%20%22)))%2C%22%2C%20%22)%2C%22%2C%20%22%2C%22%22)))%2F2)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20find%20it%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187847%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187847%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20has%20to%20be%20a%20formula.%20I%20can't%20use%20the%20Find%20and%20Replace%20dialog%20box.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187838%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187838%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Chris%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EI%20can%20give%20you%20an%20easy%20and%20fairly%20good%20solution%20away%20from%20the%20VBA%2C%20which%20can%20complete%20the%20task%20by%2099%25.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3EThis%20is%20done%20by%20using%20the%20Find%20and%20Replace%20tool.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20all%20the%20targeted%20cells%3C%2FP%3E%3CUL%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Press%20%3CSTRONG%3ECtrl%2BH%3C%2FSTRONG%3Eto%20open%20the%20Find%20and%20Replace%20tool%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20In%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3Ebox%2C%20type%3A%26nbsp%3B%20%3CSTRONG%3E%7C*%3B%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20In%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3Ebox%2C%20type%3A%26nbsp%3B%20%3CSTRONG%3E%2C%3C%2FSTRONG%3E(Comma%20and%20space)%2C%20and%20then%20press%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20result%20will%20show%20as%20follows%3A%3C%2FP%3E%3CP%3EAnnouncements%2C%20Personal%20Information%2C%20Personnel%20Statistical%20Reporting%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20can%20use%20this%20formula%20to%20remove%20the%20last%20comma%20and%20space%20from%20the%20string.%3C%2FP%3E%3CPRE%3E%3CSTRONG%3E%3DSUBSTITUTE(A1%2C%22%2C%20%22%2C%22%22%2C(LEN(A1)-LEN(SUBSTITUTE(A1%2C%22%2C%20%22%2C%22%22)))%2F2)%3C%2FSTRONG%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1174207%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174207%22%20slang%3D%22en-US%22%3E%3CP%3EGorgeous%20solution%20thank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1653035%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20text%20between%20two%20characters%20multiple%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1653035%22%20slang%3D%22en-US%22%3ELike%20this%20easier.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3ETurkish%3A%3CBR%20%2F%3E%3DMET%C4%B0NB%C4%B0RLE%C5%9ET%C4%B0R(%22%2C%20%22%3B1%3BE%C4%9EERHATA(%C4%B0ND%C4%B0S(KIRP(PAR%C3%87AAL(YER%C4%B0NEKOY(%22%7C%22%26amp%3BYER%C4%B0NEKOY(%24A%241%3B%22%3B%22%3B%22%7C%22)%3B%22%7C%22%3BY%C4%B0NELE(%22%20%22%3B255))%3BSATIR(%241%3A%246)*255%3B255))%3BSATIR(A1%3AA3)*2-1)%3B%22%22))%3CBR%20%2F%3E%3CBR%20%2F%3EEnglish%3A%3CBR%20%2F%3E%3DTEXTJOIN(%22%2C%20%22%3B1%3BIFERROR(INDEX(TRIM(MID(SUBSTITUTE(%22%7C%22%26amp%3BSUBSTITUTE(%24A%241%3B%22%3B%22%3B%22%7C%22)%3B%22%7C%22%3BREPT(%22%20%22%3B255))%3BROW(%241%3A%246)*255%3B255))%3BROW(A1%3AA3)*2-1)%3B%22%22))%3C%2FLINGO-BODY%3E
New Contributor

I have a column that has a text string with various lengths. What I'm trying to do is remove text that starts with "|" and ends with ";". The text between those two characters is always going to be 36 characters. There can be multiple occurrences and what is between the two characters will vary from row to row. I cannot use VB only a formula.

Example:

Here is my text string: 

 

Announcements|95029fcd-6b68-45bf-9f80-a2b2d90540f3;Personal Information|8096f02e-25e3-4416-8dbb-b2a58d309d4e;Personnel Statistical Reporting|9f1a73f0-5ce6-4abb-9fe1-44ab59350708;

 

What I need to do is remove from the string what is between "|" and ";" so my new text string looks like this:

 

Announcements, Personal Information, Personnel Statistical Reporting

 

With the formula below I get this. Only one instance is removed.

 

Announcements, Personal Information|8096f02e-25e3-4416-8dbb-b2a58d309d4e;Personnel Statistical Reporting|9f1a73f0-5ce6-4abb-9fe1-44ab59350708


=IFERROR(SUBSTITUTE(A1,MID(LEFT(A1,FIND(";",A1)),FIND("|",A1),LEN(A1)),", "),A1)

 

Thanks.

 

 

6 Replies

Hi Chris,

 

I can give you an easy and fairly good solution away from the VBA, which can complete the task by 99%.

 

This is done by using the Find and Replace tool.

 

Select all the targeted cells

  •     Press Ctrl+H to open the Find and Replace tool
  •     In Find what box, type:  |*;
  •     In Replace with box, type:  , (Comma and space), and then press Replace All

 

This result will show as follows:

Announcements, Personal Information, Personnel Statistical Reporting,

 

But you can use this formula to remove the last comma and space from the string.

=SUBSTITUTE(A1,", ","",(LEN(A1)-LEN(SUBSTITUTE(A1,", ","")))/2)

 

Hope that helps

Haytham

It has to be a formula. I can't use the Find and Replace dialog box. Thanks.

Best Response confirmed by Chris Langham (New Contributor)
Solution

Hi Chris,

 

Please try this very long formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "),", ","",(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "),", ","")))/2)

 

And find it in the attached file.

 

Regards

That worked...Thanks.

Gorgeous solution thank you @Haytham Amairah 

Like this easier.

Turkish:
=METİNBİRLEŞTİR(", ";1;EĞERHATA(İNDİS(KIRP(PARÇAAL(YERİNEKOY("|"&YERİNEKOY($A$1;";";"|");"|";YİNELE(" ";255));SATIR($1:$6)*255;255));SATIR(A1:A3)*2-1);""))

English:
=TEXTJOIN(", ";1;IFERROR(INDEX(TRIM(MID(SUBSTITUTE("|"&SUBSTITUTE($A$1;";";"|");"|";REPT(" ";255));ROW($1:$6)*255;255));ROW(A1:A3)*2-1);""))