I have many excel files that contain names for a genealogy project. These
files were done by various people over the last 15-20 years and the names are
in different formats. (I am loading these files into an Access database.)
Since most of the files are entered with a period after initials , I am
changing the files that do not have them entered this way to be consistant
and also to match the name if it is already in the datbase. Here are some
examples I am now changing manually:
John B (change to John B.)
B John (change to B. John)
A (Change to A.)
Is there a way to find these single-character initials? Is there a way to
programatically add the period?
Thanks in advance if you can help, Sherry

On Sun, 23 Dec 2007 14:27:00 -0800, SherryScrapDog
wrote:

I have many excel files that contain names for a genealogy project. These
files were done by various people over the last 15-20 years and the names are
in different formats. (I am loading these files into an Access database.)
Since most of the files are entered with a period after initials , I am
changing the files that do not have them entered this way to be consistant
and also to match the name if it is already in the datbase. Here are some
examples I am now changing manually:
John B (change to John B.)
B John (change to B. John)
A (Change to A.)
Is there a way to find these single-character initials? Is there a way to
programatically add the period?
Thanks in advance if you can help, Sherry


Here's one macro that might do what you require.

It replaces every "single" character that is not already followed by a dot, to
one that is:

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

The routine is case insensitive.

If you wanted to standardize the results using Proper case, you could do
something like:

======================================
Option Explicit
Sub AddDot()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "\b([A-Z])\b(?!\.)"
For Each c In Selection
c.Value = Application.WorksheetFunction.Proper(re.Replace(c. Text, "$1."))
Next c
End Sub
==============================
--ron

A little convuluted but it seems to work. Just make sure that the column to
the right is clear first.
1st it separates the textthen puts the dotthen puts it back
togetherdeletes the helper column
Sub putdot()
Range("J2:J4").TextToColumns Destination:=Range("J2"), _
DataType:=xlDelimited, Space:=True

For Each c In Range("j2:k4")
If Len(c) = 1 Then c.Value = c & "."
Next c
For Each c In Range("j2:j4")
c.Value = c & " " & c.Offset(, 1)
Next c
columns("k").delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryScrapDog" wrote in message
...
I have many excel files that contain names for a genealogy project. These
files were done by various people over the last 15-20 years and the names
are
in different formats. (I am loading these files into an Access database.)
Since most of the files are entered with a period after initials , I am
changing the files that do not have them entered this way to be consistant
and also to match the name if it is already in the datbase. Here are some
examples I am now changing manually:
John B (change to John B.)
B John (change to B. John)
A (Change to A.)
Is there a way to find these single-character initials? Is there a way to
programatically add the period?
Thanks in advance if you can help, Sherry

Ron, Thanks so much! Worked exactly right. This is my first macro, and what
a success! Many thanks, Sherry

"Ron Rosenfeld" wrote:

On Sun, 23 Dec 2007 14:27:00 -0800, SherryScrapDog
wrote:

I have many excel files that contain names for a genealogy project. These
files were done by various people over the last 15-20 years and the names are
in different formats. (I am loading these files into an Access database.)
Since most of the files are entered with a period after initials , I am
changing the files that do not have them entered this way to be consistant
and also to match the name if it is already in the datbase. Here are some
examples I am now changing manually:
John B (change to John B.)
B John (change to B. John)
A (Change to A.)
Is there a way to find these single-character initials? Is there a way to
programatically add the period?
Thanks in advance if you can help, Sherry


Here's one macro that might do what you require.

It replaces every "single" character that is not already followed by a dot, to
one that is:

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

The routine is case insensitive.

If you wanted to standardize the results using Proper case, you could do
something like:

======================================
Option Explicit
Sub AddDot()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "\b([A-Z])\b(?!\.)"
For Each c In Selection
c.Value = Application.WorksheetFunction.Proper(re.Replace(c. Text, "$1."))
Next c
End Sub
==============================
--ron

Don, thanks for responding! I got the earlier reply from Ron and did his
macro, which worked great, before I saw your response. I really appreciate
the response! Sherry

"Don Guillett" wrote:

A little convuluted but it seems to work. Just make sure that the column to
the right is clear first.
1st it separates the textthen puts the dotthen puts it back
togetherdeletes the helper column
Sub putdot()
Range("J2:J4").TextToColumns Destination:=Range("J2"), _
DataType:=xlDelimited, Space:=True

For Each c In Range("j2:k4")
If Len(c) = 1 Then c.Value = c & "."
Next c
For Each c In Range("j2:j4")
c.Value = c & " " & c.Offset(, 1)
Next c
columns("k").delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryScrapDog" wrote in message
...
I have many excel files that contain names for a genealogy project. These
files were done by various people over the last 15-20 years and the names
are
in different formats. (I am loading these files into an Access database.)
Since most of the files are entered with a period after initials , I am
changing the files that do not have them entered this way to be consistant
and also to match the name if it is already in the datbase. Here are some
examples I am now changing manually:
John B (change to John B.)
B John (change to B. John)
A (Change to A.)
Is there a way to find these single-character initials? Is there a way to
programatically add the period?
Thanks in advance if you can help, Sherry

On Sun, 23 Dec 2007 18:36:00 -0800, SherryScrapDog
wrote:

Ron, Thanks so much! Worked exactly right. This is my first macro, and what
a success! Many thanks, Sherry

You're most welcome. Glad to help. Thanks for the feedback.
--ron