Two question on working with text

M

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
 
L

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top