How to do a global name change

D

Deborragh

I like to create a macro that would format all of the names in a column to
look like the example below.

Example:

Change WOODS CYNTHIA to Woods, Cynthia
 
R

Rick B

What about
St. Johns, Phil?

This is generally very hard to do 100%.

But, this would typically be done using an update query to fix he entries
int he table. Not sure how a macro would help in this situation.

For more details on the way to do it using an update query, do a search.
This is asked and answered about once a week!

You basically use the INSTR function to find the first space " " and then yo
ureplace that with a comma and a space.
 
J

John Vinson

I like to create a macro that would format all of the names in a column to
look like the example below.

Example:

Change WOODS CYNTHIA to Woods, Cynthia
Click to expand...

A Macro is not an appropriate tool; you want an Update Query instead.
Rick's concern about last names containing blanks is well taken; there
are quite common names such as "De La Cruz" and "Van Steen" which will
require manual intervention.

That said, you might want to go a step further: if you have separate
fields for LastName and FirstName, you can easily concatenate them as
either "Woods, Cynthia" or "Cynthia Woods", search independently by
first and last name, and so on. Consider adding FirstName and LastName
fields to your table and running an Update query updating FirstName to

StrConv(Trim(Mid([fullname], InStr([fullname], " "))),3)

and LastName to

StrConv(Left([fullname], InStr([fullname], " ") - 1),3)


This will use the string handling functions Mid and Left, the string
finding function InStr, and the case-conversion function StrConv to
make the changes you request.

Then create a Query with a criterion

LIKE "* *"

on FirstName to find possible cases where a last name has been split;
fix these manually.

John W. Vinson[MVP]
 
Top