How Do? Take two words in cell 1 and slpit them to cell 2 & 3

[jermsalerms]

I want to take a clients 1st and last name that populates in cell 1 and
break it apart so that cell 2 only shows the last name and cell 3 shows
only the first name.

How do I write a formula to fill in the 1st word vs the 2nd word in
cell 1...also some have a middle initial that I do not want to be
included in either but some do not so I need it to recognize the
difference.

 

[Bob Phillips]

B1: =LEFT(A1,FIND(" ",A1)-1)

and

C1: =RIGHT(A1,LEN(A1)-FIND(" ",A1))

or maybe

C1: =SUBSTITUTE(A1,B1&" ","")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

This is the simple solution:
Column 1 = Full Name
Column 2 = LEFT(Column1,FIND(" ",Column1,1))
Column 3 = =RIGHT(Column1,LEN(Column1)-LEN(Column2))

Basically the formula for col 2 says take anything in column 1 until you hit
the first space and then for col 3 take col 1 and remove a certain number of
characters from the left side equal to the legnth of col 2.

You can run into problems if you have multiple spaces, like someone has a
midlle initial, but I hope it helps.

 

[Guest]

To extract the First Name:

=LEFT(A1,FIND(" ",A1,1)-1)

To extract the Last Name:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

HTH,
Elkar

 

[Roger Govier]

Hi

Try
In B1
=LEFT(A1,FIND(" ",A1)-1)
In C1
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,
MID(A1,FIND(" ",SUBSTITUTE(A1," ","*",1))+1,255),
MID(A1,FIND(" ",A1)+1,255))

--
Regards

Roger Govier


"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...

 

[jermsalerms]

Your formula works great but I found that some of my contacts have Jr,
Sr., etc.

With your formula what I get for the last name is just Jr.

What would make it reflect say "Smith Jr." for example


Thanks

 

[Roger Govier]

Hi

Life is never easy is it?
What I would do, is first do a Search & Replace.
Ctrl+H Find Jr. (that is a space followed
by Jr.)
Replace _Jr. (underscore followed by
Jr.)
Replace All

Repeat for Sr.

Use my formula to do your split.
Then reverse the Find & Replace to change the underscore back to a
space.


--
Regards

Roger Govier


"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

I want to take a clients 1st and last name that populates in cell 1 and
break it apart so that cell 2 only shows the last name and cell 3 shows
only the first name.

How do I write a formula to fill in the 1st word vs the 2nd word in
cell 1...also some have a middle initial that I do not want to be
included in either but some do not so I need it to recognize the
difference.

You could use regular expressions to handle all sorts of variations.

First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then for example, given:

John Doe
Ms John Doe
John J Doe
Mr. John Doe, Jr
Dr John J Doe, Jr
John Doe MD
John Doe Ph.D.



First Name:

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

Last Name including the Title

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

=================================

Rules:

Any first word will be omitted that either ends with a dot (.) or, if it starts
with an M or a D, and is followed by 0 to 2 letters (eg. Mrs. Ms Ms. M M. Dr
Dr. should all be excluded).

This, purposely will also exclude leading initials:

J. John Doe will also --> John for a first name. (This could be changed).

Various titles are delineated specifically, as I could not think of a general
rule that would include them all.


--ron

 

[jermsalerms]

The expression works for the first name but returns as #VALUE on the
last name. I am not familiar with expressions. Any idea what is causing
this?

 

[jermsalerms]

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)(\.?))?$"

 

[Ron Rosenfeld]

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)(\.?))?$")

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

 

[jermsalerms]

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?

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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