Two question on working with text

[Marsh]

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

 

[Luke M]

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,". "))

 

[Ashish Mathur]

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