Help making a period appear only if there is a character in the cell.

[TWTHOMAS]

I am using this formula to get a middle initial from a name in column A

Names in Column A are like:

Thomas Dan J
Jones Susan
Ayers John B II

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&"."

I want to only have the period (".") appear if there is a middle initial.

Currently Susan Jones has a lone . in the middle initial field because she doesn't have a middle initial. Is there a way to make that period conditional based on if there is a character available?

 

[Peter_SSs]

1. This still leaves a period in Column D when there is no middle initial or suffix. (i.e. Doe John)

Not so as far as I can see. Look at Jones Susan in my screen shot.

2. This also shows #VALUE! where the formula appears and there is no name data but only in columns B and D.

Change my column B formula to this:

=LEFT(A2,FIND(" ",A2&" ")-1)

 

[TWTHOMAS]

The change worked perfectly. Thanks

The issue with the period is not your formula as it turns out. The extra period is showing up with the orginal data
has an additional space after the last character in the sting. For example (I will sub % for space so you can see what I mean)

Jones%Susan%% results in a period in column D
Jones%Susan% or Jones%Susan does not result in the period.

Unfortunately for me I don't create that data so unless there is a way to adjust the formula my only
choice may be to select the Column and use a Find and Replace two spaces with once space.

Not so as far as I can see. Look at Jones Susan in my screen shot.

 

[Peter_SSs]

For column D, try this change

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,100))&IF(MID(TRIM(A2)&" ",LEN(B2&C2)+4,1)=" ",".","")

 

[TWTHOMAS]

Excellent results!
Thanks

For column D, try this change

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,100))&IF(MID(TRIM(A2)&" ",LEN(B2&C2)+4,1)=" ",".","")