separating firstname & lastname & turn all to capital letters

[aboiy]

Hi to all,

I want to separate the firstname & lastname in one column
to columns and turn its letter to capital.

Column A
Aboiy del rio
Mark Anthony Bautista

Result should display:
Column A B
Lastname Firstname
DEL RIO ABOIY
BAUTISTA MARK ANTHONY

The only problem is that sometimes there are two
firstnames and also there are two lastname.

I knew you can come up for a way to solve this.

Thanks and regards.

aboiy

 

[Bob Phillips]

If there could be two first or two last names, we need a rule to identify
when the middle name is first, and when it is last.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Aboiy

Try this formula

Assume your data is in cell A1 and copy these formulas in
B1 & C1 to get the first and last names.

=UPPER(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)))

=UPPER(LEFT(A1,FIND(" ",A1)))

I don't think any of the formulas could identify which
name has 2 first names which has 2 surnames......etc.

Good luck.
Arkash

 

[aboiy]

Hi Arkash,

I think i have to manually separate those with two
firstnames and lastnames, but can you give me a formula
wherein it can get at least 2 names to be placed in one
column, either for LEFT OR RIGHT func.

Regards

aboiy

 

[Bob Phillips]

Those formulae do juts that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

I'd suggest instead using Data/Text to Columns/Delimited/Space to
separate the names.

That way the names that have either two given names or two surnames will
separate into 3 columns rather than 2, and be easier to spot.

 

[David McRitchie]

You can have a first and second name, and you could
have two part to a lastname -- these can cause a problem.
You can perhaps join parts of last names together with something
like change all " de " to " de~" with Ctrl+H
and the van, von, di, etc. as you might see in your data.

then separate out the last name with a macro SepLastTerm in
http://www.mvps.org/dmcritchie/excel/join.htm#SepLastTerm

 

[aboiy]

Thank you all for giving your time and sharing your tips.

Regards

aboiy