On 2002-02-19 14:27, msvec wrote:
This is very similar to my first attempt at this. Problem is that arrays don't work in a SUBSTITUTE function/formula...only the first value in the array does, which makes sense since it has an 'instance' criteria, although when omitted represent all.
BUT, let me know if I'm wrong or missing something. What i've resorted to is to using my original formula against an OR array containing the strings i want out. if it matches, pull the 2nd to last text enclosed by spaces, else, my formula.
any thoughts.
On 2002-02-19 14:10, Aladin Akyurek wrote:
OK, here we go. What follows is an essential part of the formulas under the link I mentioned. Apparently, you need a shorter version.
=TRIM(SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr.",", POD",", PhD."},""))))),""))
You need to expand the {"Jr.",", POD",", PhD."} bit to remove other undesired substrings.
Aladin