Add a period to name initials

[SherryScrapDog]

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 Rosenfeld]

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 Guillett]

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

 

[SherryScrapDog]

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

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

 

[SherryScrapDog]

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

 

[Ron Rosenfeld]

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