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
<(E-Mail Removed)> 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 text>then puts the dot>then puts it back
together>deletes 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
(E-Mail Removed)
"SherryScrapDog" <(E-Mail Removed)> wrote in message
news:4B966F83-2D8C-4F17-8066-(E-Mail Removed)...
>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
> <(E-Mail Removed)> 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 text>then puts the dot>then puts it back
> together>deletes 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
> (E-Mail Removed)
> "SherryScrapDog" <(E-Mail Removed)> wrote in message
> news:4B966F83-2D8C-4F17-8066-(E-Mail Removed)...
> >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
<(E-Mail Removed)> 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