Placing Periods in Excel

[sconley1]

I am trying to place a period after a middle initial and after a title. =A1&"." works however some don't have a middle initial or the name is spelled out so I do not want a period there. Any help with this would be very much appreciated!




Column A Column B Column C Column D

Mr Jo Henry Smith
Mark W Walsh
Mrs Ashley Beall

 

[sconley1]

Column A is the title (Mr)
Column B is the First Name (Jo)
Column C is the Middle Name (Henry)
Column D is the Last name (Smith)

 

[Joe4]

Welcome to the Board!

Try this:

=A1 & " " & B1 & " " & IF(LEN(C1)=1,C1 & ". ",IF(C1="","",C1 & " ")) & D1

 

[sconley1]

Thank you Joe!!! When I use your formula is puts the period where is should for middle initial, but doesn't put it after title. Example Robert Robenseifner S. Mr doesn't have the period after the Mr.

[TABLE="width: 335"]
<colgroup><col width="446" style="width: 335pt; mso-width-source: userset; mso-width-alt: 16310;"> <tbody>[TR]
[TD="width: 446, bgcolor: transparent"]Robert Robenseifner S. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roger Kezar C. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]David Lacovone J. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Patrick Wardlaw N. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Robert Gill B. Maj[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]L Higbee T. LtCol[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Robert Jackson [/TD]
[/TR]
</tbody>[/TABLE]

 

[sconley1]

Thank you Joe!!! When I use your formula is puts the period where is should for middle initial, but doesn't put it where it should in the title. Example Robert Robenseifner S. Mr doesn't have the period after the Mr.

[TABLE="width: 335"]
<colgroup><col width="446" style="width: 335pt; mso-width-source: userset; mso-width-alt: 16310;"><tbody>[TR]
[TD="width: 446, bgcolor: transparent"]Robert Robenseifner S. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roger Kezar C. Mr[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

If you want to "fix" the data in place, then you can use this macro...

Sub AddPeriodOnAbbreviations()
  Dim Cell As Range
  For Each Cell In Range("A1").CurrentRegion
    If Len(Cell.Value) Then
      If Cell.Column = 1 Or Len(Cell.Value) = 1 Then Cell.Value = Cell.Value & "."
    End If
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (AddPeriodOnAbbreviations) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Joe4]

Try this:

=IF(A1="","",IF(A1="Miss","Miss ",A1&". "))&B1&" "&IF(LEN(C1)=1,C1&". ",IF(C1="","",C1&" "))&D1

 

[sconley1]

[TABLE="width: 212"]
<colgroup><col width="283" style="width: 212pt; mso-width-source: userset; mso-width-alt: 10349;"> <tbody>[TR]
[TD="width: 283, bgcolor: transparent"]Robert. Robenseifner S. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Robert. Robenseifner S. Mr

Gives me a period after the first name [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

[TABLE="class: cms_table, width: 212"]
<tbody>[TR]
[TD="width: 283, bgcolor: transparent"]Robert. Robenseifner S. Mr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Robert. Robenseifner S. Mr

Gives me a period after the first name [/TD]
[/TR]
</tbody>[/TABLE]

It seems your specifications have changed.
In your first two posts, the title was in column A.
What exactly is your column structure?

 

[jtakw]

I am trying to place a period after a middle initial and after a title. =A1&"." works however some don't have a middle initial or the name is spelled out so I do not want a period there. Any help with this would be very much appreciated!




Column A Column B Column C Column D

Mr Jo Henry Smith
Mark W Walsh
Mrs Ashley Beall

Column A is the title (Mr)
Column B is the First Name (Jo)
Column C is the Middle Name (Henry)
Column D is the Last name (Smith)

It seems your specifications have changed.
In your first two posts, the title was in column A.
What exactly is your column structure?

Agree with Joe4, you have changed your layout, please post the correct information/question so you're not wasting people's time who are trying to help you.

This only works with your ORIGINAL information in the first 2 posts:

Book1
	A	B	C	D	E	F	G	H
1	Title	First Name	Middle Name	Last Name
2	Mr	Jo	Henry	Smith		Mr. Jo Henry Smith		18
3		Mark	W	Walsh		Mark W. Walsh		13
4	Mrs	Ashley		Beall		Mrs. Ashley Beall		17
Sheet13

Cell Formulas
Range	Formula
F2	=IF(A2="","",A2&". ")&B2&" "&IF(LEN(C2)=1,C2&". "&D2,IF(C2="",D2,C2&" "&D2))
H2	=LEN(F2)