! removing initials and titles from name cell !

[Rayiw]

Hi,

I'm sure what I'm asking must have a simple solution, but I can't work out
how to achieve it so thought I'd ask the experts.

I have an addess list. It has names and some with initials, and titles.
I'm looking for a macro, or some simple way to remove these characters from
each name cell.

Example:

Bob A Lastnam & Jenny B secondname Jr
Fred Blogs & John J Ashton ii

What I want as the result is:
Bob Lastname & Jenny secondname
Fred Blogs & John Ashton

Any suggestions on an elegant way to acheive this.

I note that doing a replace on " "A" " with " " works within the cell, but
not if the A is the last character in the cell.

Thanks
Ray
(e-mail address removed)

 

[Dave O]

Hi, Ray-
It may not be elegant, but here's a workaround, at least:
If your Names are all in column A, for instance, insert a new Column B
and for each name cell write this formula:
=A1 & " "
This will add a space to the end of each name entry. Then copy column
B and paste it back over itself as values, converting all the formulas
to text. This will allow you to capture a "Jr", for instance, when it
occurs in the middle of a name entry and at the end.