If..The Statement to automatically format a name

  • Thread starter Thread starter G. B. Needs Excel Help
  • Start date Start date
G

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
 
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
 
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
Click to expand...

Of course, for a name like:

H J Smith

or

J Gimper

Your routine doesn't work.
--ron
 
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
Click to expand...

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
 
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
 
=IF(ISNUMBER(SEARCH(" ? ",A1)),SUBSTITUTE(A1,MID(A1,SEARCH(" ?
",A1)+1,2),MID(A1,SEARCH(" ? ",A1)+1,1)&". "),A1)
 
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
 
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.
 
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
 
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
Click to expand...

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