On 2002-02-19 14:39, IML wrote:
I tried this and couldn't get it to work. To prevent user error such as mine with capitilzation, you could tweak this everso slightly with
=TRIM(SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),{"JR.",", POD",", PHD."},""))))),""))
very nice, Aladin.
I devised this formula (of which an extended description how it works is available in the Archives) to remove the initial or closing nums from a string: aky1234 --> aky or the inverse operation: aky1234 --> 1234. The case to which I'm now applying it is a bit special. UPPER'ing is only needed, it seems, if you also have cases like Denise Dukakof, PHD. I advised expanding the constant array itself with such cases:
{"Jr.",", POD",", PhD.","JR.",", POD",", PHD"}
Both tactics should work, but maybe the latter is safer (untested).
Aladin