Thank you all so much for all your replies. I will try one by one to see if
something works for me (I am not so good when it comes to VBA and Macros :-)
but will venture this out). I will report back.

BTW: the names are listed as follows: first_name middle_initial. last_name
(althought the middle initial is missing in some of the names).

"Ron Rosenfeld" wrote:

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