name last name order

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 6, 2002

• #2

=WMID(A1,WORDCOUNT(A1))&" "&WMID(A1,1,WORDCOUNT(A1)-1)

for which you need to install the morefunc.xll add-in, downloadable from

http://longre.free.fr/english/index.html

 

Upvote 0

H

huorsa

Board Regular

Joined

Feb 15, 2002

Messages

101

• Nov 6, 2002

• #3

It works, just have a little problem, some people have two last name, per example.

John Smith Tuggle

and when I apply your formula it appears
Tuggle Jonh Smith
and it should be
Smith Tuggle John

How can nI fix this

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 6, 2002

• #4

On 2002-11-06 12:20, huorsa wrote:
It works, just have a little problem, some people have two last name, per example.

John Smith Tuggle

and when I apply your formula it appears
Tuggle Jonh Smith
and it should be
Smith Tuggle John

How can nI fix this

Click to expand...

That's difficult, unless there is some reularity by which middle names can be distinguished from the two last name cases, such as: all middle names consist of a letter followed by a period and last names are fully written out.

 

Upvote 0

H

huorsa

Board Regular

Joined

Feb 15, 2002

Messages

101

• Nov 6, 2002

• #5

I don't know if this makes it easier, suppose all names have 4 words and the last two words are the last name.

Peter John Smith tuggle

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 6, 2002

• #6

On 2002-11-06 12:55, huorsa wrote:
I don't know if this makes it easier, suppose all names have 4 words and the last two words are the last name.

Peter John Smith tuggle

Click to expand...

=WMID(A1,3,2)&" "&WMID(A1,1,2)

or

=WMID(A1,3,2)&", "&WMID(A1,1,2)

if your entries that regular.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Nov 6, 2002

• #7

Hi huorsa:

In addition to the formulations suggested by Aladin, try

=IF(ISERROR(FIND(".",A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,1))&" "&LEFT(A1,FIND(" ",A1,1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1,1)-1)&" "&LEFT(A1,FIND(".",A1,1)))

This proposed formulation will handle, double last names, single last names, as well as middle initial in the name. See the worksheet simulation ...

Book1
	A	B	C	D
1	PeterWilson	WilsonPeter
2	MarkA.Church	ChurchMarkA.
3	ArnoldParker	ParkerArnold
4	BenjaminThompson	ThompsonBenjamin
5	JohnSmithTuggle	SmithTuggleJohn
Sheet4

</SPAN>

Regards!
Yogi Anand

 

Upvote 0