separating firstname & lastname & turn all to capital letters

  • Thread starter Thread starter aboiy
  • Start date Start date
A

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
 
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)
 
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
 
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
 
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)
 
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.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

truncate of name & date conversion into the text 5
Multiple Functions across Two Columns 1
Is there a way to separate FirstName and LastName ? 4
Search lastname + firstname (search on uppercase) 2
Separate cell text FirstnameLastname into two columns 3
Extracting text to from one cell to create columns and rows 1
Text to Columns Problem 4
A lingering question about suppressing ", " in concatenated expression (=lastname&", "&firstname) 1

Back
Top