Two question on working with text
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 |
Two question on working with text
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 |
Two question on working with text
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 |
All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com