Separating prefixes in names

L

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top