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?

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

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

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


"Susan" wrote:

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?

hey!
:)
susan

On May 14, 12:43 pm, Susan wrote:
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?

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" wrote in message
...
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?

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

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 InsertModule. 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

"Susan" wrote in message ...
| 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

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
--
Gary''s Student - gsnu200721

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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

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"



--
Regards,

Peo Sjoblom


"Susan" wrote in message
...
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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

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?

"Niek Otten" wrote:

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 InsertModule. 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

"Susan" wrote in message ...
| 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

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

"Peo Sjoblom" wrote:

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"



--
Regards,

Peo Sjoblom


"Susan" wrote in message
...
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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

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


--
Regards,

Peo Sjoblom



"Susan" wrote in message
...
The function below does not work. I get #NAME? when I type it into the
cells.

"Peo Sjoblom" wrote:

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"



--
Regards,

Peo Sjoblom


"Susan" wrote in message
...
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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

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

=initalizer(A1)

and make sure a name is in A1
--
Gary''s Student - gsnu200721


"Susan" wrote:

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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

On Mon, 14 May 2007 09:43:01 -0700, Susan
wrote:

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

On Mon, 14 May 2007 10:22:02 -0700, Teethless mama
wrote:

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

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


"Susan" wrote:

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?

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

On Mon, 14 May 2007 13:34:00 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org wrote:

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

On Mon, 14 May 2007 10:16:04 -0700, 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


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

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
--
Gary''s Student - gsnu200721


"Susan" wrote:

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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

On Mon, 14 May 2007 19:57:24 -0400, Ron Rosenfeld
wrote:

On Mon, 14 May 2007 09:43:01 -0700, Susan
wrote:

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

This worked. Thanks so much.

"Gary''s Student" wrote:

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

=initalizer(A1)

and make sure a name is in A1
--
Gary''s Student - gsnu200721


"Susan" wrote:

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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721

When I put the formula in, any name that has a middle initial with Jr. after
it, drops the Jr.

"Susan" wrote:

This worked. Thanks so much.

"Gary''s Student" wrote:

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

=initalizer(A1)

and make sure a name is in A1
--
Gary''s Student - gsnu200721


"Susan" wrote:

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?

"Gary''s Student" wrote:


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
--
Gary''s Student - gsnu200721