Last Name
=REGEX.MID(A5,"\w+((,?\s)(Jr|Sr|II|III|IV|M\.?D|PH \.?D)(\.?))?$")

seems to work

I took out the string in red

=REGEX.MID(A5,"\w+((,?\s)(Jr|Sr|II|III|IV|M\.?D|(? i)PH\.?D)(\.?))?$")


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498521

On Fri, 6 Jan 2006 10:40:16 -0600, jermsalerms
wrote:


Last Name
=REGEX.MID(A5,"\w+((,?\s)(Jr|Sr|II|III|IV|M\.?D|P H\.?D)(\.?))?$")

seems to work

I took out the string in red

=REGEX.MID(A5,"\w+((,?\s)(Jr|Sr|II|III|IV|M\.?D|( ? i)PH\.?D)(\.?))?$")

I don't know what's in red, as my newsreader does not reproduce that.

OK I went to the site where you posted originally and I see what has happened.
For some reason, and it is not in my newsreader, your site added an extraneous
space.

This fragment: M\.?D|(? i)PH\.?D)

There should NOT be a space between the '?' and the 'i'

The fragment should read:

M\.?D|(?i)PH\.?D)

I don't understand why that space is there as it was not sent out from here
that way. Nor does it appear to be a word wrap issue.

The (?i) parameter allows case-insensitive matching of PHD so that Ph.D., PH.D.
PHD PhD should all match.

On my original, if you remove that space, it should work fine.

--ron

Ron,

I found that when a persons name starts with M it skips the first name
and inputs the middle initial

for example

MICHEAL B SMITH

returns

B

How would this be corrected?


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498521

On Fri, 6 Jan 2006 12:06:03 -0600, jermsalerms
wrote:


Ron,

I found that when a persons name starts with M it skips the first name
and inputs the middle initial

for example

MICHEAL B SMITH

returns

B

How would this be corrected?

This should do it, and will also remove the space following the FN.

=REGEX.MID(A1,"(?!D|M(\w{0,2}(\.\s|\s)))(?<=\s|^)\ w+(?=\s)")

Did you also see my note about the Last Name issue?


--ron

On Fri, 6 Jan 2006 12:06:03 -0600, jermsalerms
wrote:


Ron,

I found that when a persons name starts with M it skips the first name
and inputs the middle initial

for example

MICHEAL B SMITH

returns

B

How would this be corrected?

Ignore my last. Use this instead:

=REGEX.MID(A1,"(?!(D|M)(\w{0,2}(\.\s|\s)))(?<=\s|^ )\w+(?=\s)")


--ron