Need Period After Initials

[Susan]

I imported a database into an Excel spreadsheet. The middle initials in the
names don't have periods after them. Is there a wildcard character that I
can use to replace initials without periods to initials with periods?

 

[Gary''s Student]

Try this small UDF:

Function initalizer(r As Range) As String
v = r.Value
s = Split(v, " ")
If UBound(s) < 2 Then
initalizer = v
Exit Function
End If
If Len(s(1)) > 1 Then
initalizer = v
Exit Function
End If
initalizer = s(0) & " " & s(1) & ". " & s(2)
End Function

 

[Teethless mama]

Assuming your data look like this in A1: John Z Doe

B1: =SUBSTITUTE(A1," ",". ",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

 

[Susan]

hey!

susan

 

[Bernie Deitrick]

Susan,

If your name strings could have 2 or more initials rather than just one:

Susan A B Smith

then this UDF will work, used like:

=MidInit(A1)

Function MidInit(myName As String) As String
Dim i As Integer
Dim myStr As String

myStr = myName
For i = 1 To Len(myName) - 2
If Mid(myName, i, 1) = " " And Mid(myName, i + 2, 1) = " " Then
myStr = Left(myName, i + 1) & "." & Mid(myName, i + 2, Len(myName))
myStr = MidInit(myStr)
End If
Next i

MidInit = myStr
End Function

HTH,
Bernie
MS Excel MVP

 

[Susan]

Where do I enter that?

 

[Niek Otten]

Hi Susan,

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Where do I enter that?
|
| "Gary''s Student" wrote:
|
| > Try this small UDF:
| >
| > Function initalizer(r As Range) As String
| > v = r.Value
| > s = Split(v, " ")
| > If UBound(s) < 2 Then
| > initalizer = v
| > Exit Function
| > End If
| > If Len(s(1)) > 1 Then
| > initalizer = v
| > Exit Function
| > End If
| > initalizer = s(0) & " " & s(1) & ". " & s(2)
| > End Function
| > --
| > Gary''s Student - gsnu200721

 

[Gary''s Student]

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.


To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


to use:
If A1 thru A5 contain:

Cher
Victor Laszo
William Randolph Hearst
William R Hearst

In B1 put:

=initalizer(A1)
and copy down

to see:

Cher
Victor Laszo
William Randolph Hearst
William R. Hearst


If the name is one or two words, no change is made.
Only if the middle name is one character long is the period added

 

[Susan]

I pasted the UDF into a module in VBE, but it hasn't done anything to the
worksheet. Is there some way I have to run it?

 

[Peo Sjoblom]

You should read the post or check out the link that was provided? You use it
as a regular function, to quote the post you replied to.

"to use:
If A1 thru A5 contain:

Cher
Victor Laszo
William Randolph Hearst
William R Hearst

In B1 put:

=initalizer(A1)
and copy down

to see:

Cher
Victor Laszo
William Randolph Hearst
William R. Hearst"

 

[Susan]

I have done all that but still don't see any change to my spreadsheet. What
do I have to do to make the function work? Does it have to be run?

 

[Susan]

The function below does not work. I get #NAME? when I type it into the cells.

 

[Peo Sjoblom]

If you get a name error then you either didn't copy the code into a regular
module or spelled it incorrectly.
Make sure you didn't paste it into a worksheet module, if it is done
correctly you won't get any name errors. Press Alt + F11, select the
workbook in the project pane to the left, then click insert > module, then
paste the code into the main window that opens. Press Alt + Q to close the
VBA editor and finally save the workbook

 

[Gary''s Student]

You need to use it like any other formula. In cell B1 enter:

=initalizer(A1)

and make sure a name is in A1

 

[Ron Rosenfeld]

I imported a database into an Excel spreadsheet. The middle initials in the
names don't have periods after them. Is there a wildcard character that I
can use to replace initials without periods to initials with periods?

One way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=REGEX.SUBSTITUTE(A1,"([A-Z])\s","[1]. ")

Note the <space> after the <.> in the last argument of the formula.

In this algorithm, a "middle initial" is defined as a capital letter followed
by a space and preceded by something that is not a letter, number or
underscore. If your data differs from that, post back.

So multiple initials in a name will have a <.> inserted.

A J Fitz A. J. Fitz
Mary R Scots Mary R. Scots
Eskimo A B Joe Eskimo A. B. Joe
John Doe John Doe

--ron

 

[Ron Rosenfeld]

Assuming your data look like this in A1: John Z Doe

B1: =SUBSTITUTE(A1," ",". ",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

But if there is no middle initial in a particular name?
--ron

 

[Ron Rosenfeld]

Susan,

If your name strings could have 2 or more initials rather than just one:

Susan A B Smith

then this UDF will work, used like:

=MidInit(A1)

Function MidInit(myName As String) As String
Dim i As Integer
Dim myStr As String

myStr = myName
For i = 1 To Len(myName) - 2
If Mid(myName, i, 1) = " " And Mid(myName, i + 2, 1) = " " Then
myStr = Left(myName, i + 1) & "." & Mid(myName, i + 2, Len(myName))
myStr = MidInit(myStr)
End If
Next i

MidInit = myStr
End Function

HTH,
Bernie
MS Excel MVP

Of course, if the "initial" is at the beginning:

F Scott Fitzgerald
--ron

 

[Ron Rosenfeld]

Try this small UDF:

Function initalizer(r As Range) As String
v = r.Value
s = Split(v, " ")
If UBound(s) < 2 Then
initalizer = v
Exit Function
End If
If Len(s(1)) > 1 Then
initalizer = v
Exit Function
End If
initalizer = s(0) & " " & s(1) & ". " & s(2)
End Function

Has a problem if there is a leading initial:

F Scott Fitzgerald,

or if there are more than three "names" or initials with at least one initial.

Eskimo A B Joe
--ron

 

[Gary''s Student]

BE VERY CAREFUL about the spelling of the function in the worksheet. I
mis-spelled it in the VBA and it must be also "mis-spelled" in the worksheet.

use

initalizer

and not

initializer

 

[Ron Rosenfeld]

I imported a database into an Excel spreadsheet. The middle initials in the
names don't have periods after them. Is there a wildcard character that I
can use to replace initials without periods to initials with periods?

One way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=REGEX.SUBSTITUTE(A1,"([A-Z])\s","[1]. ")

Note the <space> after the <.> in the last argument of the formula.

In this algorithm, a "middle initial" is defined as a capital letter followed
by a space and preceded by something that is not a letter, number or
underscore. If your data differs from that, post back.

So multiple initials in a name will have a <.> inserted.

A J Fitz A. J. Fitz
Mary R Scots Mary R. Scots
Eskimo A B Joe Eskimo A. B. Joe
John Doe John Doe

--ron

Typo. Should be:

=REGEX.SUBSTITUTE(A1,"\b([A-Z])\s","[1]. ")
--ron