| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If then statement and time format | Excel Discussion (Misc queries) | |||
| If statement and cell format | Excel Discussion (Misc queries) | |||
| if statement format cell | Excel Worksheet Functions | |||
| Not sure how to format this if statement | Excel Discussion (Misc queries) | |||
| How to add date format into if statement? | Excel Discussion (Misc queries) | |||
Linear Mode
