If..The Statement to automatically format a name
 

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

If..The Statement to automatically format a name
 

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
--
Gary''s Student - gsnu200908


"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

On Sun, 8 Nov 2009 15:55:01 -0800, Gary''s Student
wrote:

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

If..The Statement to automatically format a name
 

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
---------------
Jacob Skaria


"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

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



"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

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" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

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

"G. B. Needs Excel Help" wrote:

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

If..The Statement to automatically format a name
 

On Wed, 11 Nov 2009 11:57:01 -0800, G. B. Needs Excel Help
wrote:

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