Separating prefixes in names

[luthierwnc]

Hi All,

I am trying to combine and organize a bunch of SL spreadsheets for a
charity. Some of the worksheets have first and last names. Some have
everything in one column which may include prefixes such as "Mr.", "Mr. and
Mrs.", "Dr. and Mrs." etc. Many of the entries don't have a prefix. I can
separate the words by using the text-to-column program but the first and last
names don't line up because of the prefixes.

I know outlook can break them up automatically but is there a way to do it
in Excel?

Thanks, sh

 

[Gary''s Student]

Select the cells that you wish to modify and run this simple macro:

Sub TitleKiller()
titles = Array("Mr. ", "Mrs. ", "Miss. ")
U = UBound(titles)
For Each r In Selection
v = r.Value
For i = 0 To U
v = Replace(v, titles(i), "")
Next
r.Value = v
Next
End Sub

As coded, the macro will only handle Mr. Mrs. and Miss.

Add additional titles to the array statement.


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm