If..The Statement to automatically format a name

[G. B. Needs Excel Help]

I am looking for an Excel if..then statement to evaluate a name and insert a
period after a single letter if the name contains a single letter. Example
John Q Smith.

Can excel do this for a list of names that are not the same length. Please
help. These are more examples.
John T James
Janice S Smith
Lola P Simpson

 

[Gary''s Student]

Select the cells you want to fix and run this small macro:

Sub AddADot()
For Each r In Selection
s = Split(r.Value, " ")
If UBound(s) = 2 Then
If Len(s(1)) = 1 Then
s(1) = s(1) & "."
r.Value = Join(s, " ")
End If
End If
Next
End Sub

 

[Ron Rosenfeld]

Select the cells you want to fix and run this small macro:

Sub AddADot()
For Each r In Selection
s = Split(r.Value, " ")
If UBound(s) = 2 Then
If Len(s(1)) = 1 Then
s(1) = s(1) & "."
r.Value = Join(s, " ")
End If
End If
Next
End Sub

Of course, for a name like:

H J Smith

or

J Gimper

Your routine doesn't work.
--ron

 

[Ron Rosenfeld]

On Sun, 8 Nov 2009 15:04:01 -0800, G. B. Needs Excel Help <G. B. Needs Excel

I am looking for an Excel if..then statement to evaluate a name and insert a
period after a single letter if the name contains a single letter. Example
John Q Smith.

Can excel do this for a list of names that are not the same length. Please
help. These are more examples.
John T James
Janice S Smith
Lola P Simpson

You can do this with a User Defined Function.

This function adds a dot "." after any single letter within your string. So,
for example, it will handle names like H J Smith, J Smith.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ReDot(A1)

in some cell.

=====================================
Option Explicit
Function ReDot(s As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
.IgnoreCase = True
.Global = True
.Pattern = "\b([A-Z])(\s|$)"
ReDot = .Replace(s, "$1. ")
End With
End Function
=========================
--ron

 

[Jacob Skaria]

If you are looking for a worksheet function try the below with your name in
cell A1 and copy down as required...

=IF(MID(A1& " xx",FIND(" ",A1& " xx")+2,1)=" ",SUBSTITUTE(A1," ","' ",2),A1)

If this post helps click Yes

 

[Teethless mama]

=IF(ISNUMBER(SEARCH(" ? ",A1)),SUBSTITUTE(A1,MID(A1,SEARCH(" ?
",A1)+1,2),MID(A1,SEARCH(" ? ",A1)+1,1)&". "),A1)

 

[Jacob Skaria]

For a period...(the earlier one added an apostrophe)

=IF(MID(A1& " xx",FIND(" ",A1& " xx")+2,1)=" ",SUBSTITUTE(A1," ",". ",2),A1)

If this post helps click Yes

 

[G. B. Needs Excel Help]

This worked! Thank you for your help. I tried your formula at work and it
worked perfectly. Thank you, Thank you, This saves alot of time editing
names.

 

[G. B. Needs Excel Help]

Jacob Skaria
Please make changes to your formula to search the name and add a period
after any single letter.

Example: T Gordon Smith and P T Smith.

Your formula worked perfectly for a middle initial. I appreciate all help.

Thank you

 

[Ron Rosenfeld]

Jacob Skaria
Please make changes to your formula to search the name and add a period
after any single letter.

Example: T Gordon Smith and P T Smith.

Your formula worked perfectly for a middle initial. I appreciate all help.

Thank you

The UDF I suggested will do exactly that.
--ron