Solved: Fix Pasted Name

[Anne Troy]

Suppose I copy a name that, unfortunately, could be in any of the following formats:

TROY, ANNE M., M.D.

TROY, ANNE M.

What I'd like is, as soon as I paste, and my cursor is sitting at the end of the line, to hit a shortcut key that'll fix the name to (in the same order as above):

ANNE TROY M., M.D.

ANNE M. TROY

Thanks tons, guys!

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=656847

[MWE]

this is an interesting problem because of the various permutations. I expanded the problem a bit to include virtually any reasonable original format including the double suffix, e.g., Anne M. Troy, M.D., PhD

My solution uses a few bits and pieces I had laying around plus some brute force code. It is not elegant, but it works. The attached zip contains both an xls test file and a Word test doc

[Zack Barresse]

MWE: The Word functions work very well. :yes Although you may want to take a look at the xls file again; I receive errors. Double check your routine names and your auto-open procedure.

[mdmackillop]

I think this call for one of these RegExp pattern searchers, which is one of these things which I almost, but not quite, completely fail to understand.

[Anne Troy]

LOL!! Function MWE_...

Too cool. Can you add it to our KB, sir?? Please!!!!!!!!!

[MWE]

MWE: The Word functions work very well. :yes Although you may want to take a look at the xls file again; I receive errors. Double check your routine names and your auto-open procedure.
You are absolutely right that the xls file was screwy. I have a VBA library of several hundred procs in my Personal.xls and this is referenced in every xls appl I develop. I keep forgetting (when I send something to others) to make sure that every proc referenced in that library is explicitly added to one of the appl code modules. Once I did that and unchecked the ref to Personal.xls, I noticed several problems:


duplicate code in both the worksheet code area and a general module
a call to a wrong proc
a call in Workbook_Open to a EvalCopy duration check proc
ref to Personal.xls was still checked
Wow! that is enough goofs for today !!:banghead:

Whatever, the revised zip file should now have a working xls file.


LOL!! Function MWE_...

Too cool. Can you add it to our KB, sir?? Please!!!!!!!!!
I have been thinking about what I might add. As per another reply to this thread, I have a fair number of MWE_... routines developed over the last few years. I try to develop code in logical, reusable pieces even if that sometimes means a little more passing of arguements. So many of these procs do quite simple things, but I never have to recode that function. Unfortunately, I tend to get something working and then make great plans to document, clean-up, etc.; and that does not happen as often as it should. I know that I am unique in this behaviour. Perhaps adding to the VBAX KB will be the incentive I need (and I would not mind a run at that quilt!)

[Anne Troy]

:D It's not difficult to do once you know how. :)

[bazzelle]

Just wanted to say thank you to every one that helped with this one. This solution works great!

David Bazzelle

[Anne Troy]

Ah! I knew Dave would show up eventually. :) He was our original asker on this question. Thanks for letting us know, Dave!