On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld
wrote:
On Sat, 15 Jul 2006 16:15:02 -0700, kikilein
wrote:
I have a column of names first name, middle initial (sometimes), last name.
I have a hard time to separate that middle initial that some of the names
have but not all. I tried the Data text to column but have a hard time
seperating the middle intitial. Any ideas on how to do it?
Thanks much.
You can do this with regular expressions.
Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr
Then, assuming the Middle Initial is preceded by a <space, may optionally be
terminated by a period (.) and is followed by a <space, you can use the
formula:
=REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)")
If the Middle Initial might be a Middle Name, then:
=REGEX.MID(A2,"(?<=\s)\w+\.?(?=\s)")
would pick up both.
You can also use regular expressions for the first and last names:
First Name: =REGEX.MID(A2,"^\w+(?=\s)")
Last Name: =REGEX.MID(A2,"\w+$")
--ron
I overlooked this in reading your first post:
If the First Name and optional Middle Initial are always followed by a comma
and <space, then you could use these formulas:
FN: =REGEX.MID(A2,"^\w+(?=,\s)")
MI: =REGEX.MID(A2,"(?<=,\s)\w\.?(?=,\s)")
LN: =REGEX.MID(A2,"\w+$")
--ron