Rick, Thanks tons! It works so well I wish I had asked this before. You
have saved me hours and hours of work. This is a volunteer project for me
and I just love working on it, but as you can probably imagine, it gets
boring going thru these files cell by cell and deleting spaces and adding
periods. My goal is to give the Society a database with data as clean as
possible. Thanks, thanks and thanks again, Sherry (I will check Ron's post
if he does respond.)

"Rick Rothstein (MVP - VB)" wrote:

Given that you indicated you used Ron Rosenfeld's routine in your earlier
thread, I modified that to add the additional functionality...

Sub AddDot()
Dim c As Range
Dim txt As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
For Each c In Selection
re.Pattern = "\b([A-Z])\b(?!\.)"
txt = re.Replace(c.Text, "$1.")
re.Pattern = "\s{2,}"
c.Value = re.Replace(txt, " ")
Next c
End Sub

Now a word of caution... this is my first regular expression construction in
some 15 years, so I am rusty. What I posted works, but I can't guarantee it
is the most efficient construction. So, check back here to see if Ron posts
a better constructed regular expression solution than this one.

Rick


"SherryScrapDog" wrote in message
...
I just had some really great help with some Excel files from this group
where
the formatting was done by various people over the last 15-20 years.
These
are names for genealogy, and I am loading many Excel files into Access so
people can search by name and soundex. I don't know if the other problem
I
am dealing with can be handled programatically, but some of the people in
the
past entered extra spaces between the first and middle names. I am going
thru the files manually now and removing the extra space. I have more
files
to do and thought it was worth asking if a macro could do this. Here are
some examples:
John William (I change to John William by using delete key)
Doris Doe Smith (I change to Doris Doe Smith)
John W. ( change to John W.)
I found many posts on this site that talks about Trim, but could not find
anything that specifically addressed just removing extra space within
text.
Thanks in advance if there is any help for me, Sherry