In Excel 2007, Column C has a list of 3500 names in the format of FirstName
LastName, but unfortuately the are approximately 1200 also have a middle
intitial, some with a period and some without a period. As is evident, this
list was compiled over a period of about 2 years and many people contributed
to it with no guidlines, and now I have been directed to fix it.

Question 1 - Can a formula be devised to extract the first four letters of
the last name?
Question 2 - Is there a means to put a period after those middle initials
that do not have one
Thank you
Marsh

Answer 1:
=MID(A2,FIND(" ",A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,FIND("
",A2)+1,1))+1,4)

Answer 2:
=IF(OR(ISNUMBER(FIND(".",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))=1),A2,REPLACE(A2,FIND(" ",A2)+2,1,". "))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marsh" wrote:

In Excel 2007, Column C has a list of 3500 names in the format of FirstName
LastName, but unfortuately the are approximately 1200 also have a middle
intitial, some with a period and some without a period. As is evident, this
list was compiled over a period of about 2 years and many people contributed
to it with no guidlines, and now I have been directed to fix it.

Question 1 - Can a formula be devised to extract the first four letters of
the last name?
Question 2 - Is there a means to put a period after those middle initials
that do not have one
Thank you
Marsh

Hi,

Try this to get the first four characters of the last name (Name assumed to
be in cell B4)

=left(trim(right(substitute(B2," ",rept(" ",99)),20)),4)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Marsh" wrote in message
...
In Excel 2007, Column C has a list of 3500 names in the format of
FirstName
LastName, but unfortuately the are approximately 1200 also have a middle
intitial, some with a period and some without a period. As is evident,
this
list was compiled over a period of about 2 years and many people
contributed
to it with no guidlines, and now I have been directed to fix it.

Question 1 - Can a formula be devised to extract the first four letters of
the last name?
Question 2 - Is there a means to put a period after those middle initials
that do not have one
Thank you
Marsh