I have a column with a middle initial in some of the cells. I would
like to add a period after the initial and the cells without an initial,
leave empty. Is there a function for this?

TIA

Substitute might work for you. If the formatting is consistent, it shouldn't be
too hard.

Is the format always FirstName<spaceInitial<spaceLastName?
And without an initial, it's always FirstName<spaceLastName?

If so, you can use Substitute to change the second space to <period<space

If you still need help, post back. Make sure you specify what format the names
are in.

--
Regards,
Fred


"gh" wrote in message
...
I have a column with a middle initial in some of the cells. I would like to
add a period after the initial and the cells without an initial, leave empty.
Is there a function for this?

TIA

Assume John M Doe is in cell A1. Use following following formula in an
adjacent cell to replace the second instance of a space with a period and a
space. Requires there to be only one space between John M and between M Doe
or ir will place the comma at the second space.

=SUBSTITUTE(A1," ",". ",2)

For names without the initial and second space, it will simply return the
name as is.

If you have instances of 2 spaces between the names and initials, you can
use Find and Replace to replace all instances of 2 spaces with one space.
Simply type 2 spaces in Find what and one space in Replace with.

--
Regards,

OssieMac


"gh" wrote:

I have a column with a middle initial in some of the cells. I would
like to add a period after the initial and the cells without an initial,
leave empty. Is there a function for this?

TIA

Fred Smith wrote:
Substitute might work for you. If the formatting is consistent, it shouldn't be
too hard.

Is the format always FirstName<spaceInitial<spaceLastName?
And without an initial, it's always FirstName<spaceLastName?

If so, you can use Substitute to change the second space to <period<space

If you still need help, post back. Make sure you specify what format the names
are in.

Fred:

The cell only has a middle initial in it or it is empty. There is not a
first or last name. So I want to check the cell for a character and if
it has one, then add a period to it. If not then do nothing.

TIA

Hi,

It appears that Fred and I both answered simultaneously on your last post
and we both interpreted you request similarly. However, if the cell only
contains one character or empty then assuming that B1 is the cell to test,
you could insert the following into an adjacent cell:-

=IF(B1<"",B1&".","")

You can then Copy the columnwith the formulas and Paste Special - Values
over the original column and then remove the column with the formula.

--
Regards,

OssieMac


"gh" wrote:

Fred Smith wrote:
Substitute might work for you. If the formatting is consistent, it shouldn't be
too hard.

Is the format always FirstName<spaceInitial<spaceLastName?
And without an initial, it's always FirstName<spaceLastName?

If so, you can use Substitute to change the second space to <period<space

If you still need help, post back. Make sure you specify what format the names
are in.

Fred:

The cell only has a middle initial in it or it is empty. There is not a
first or last name. So I want to check the cell for a character and if
it has one, then add a period to it. If not then do nothing.

TIA