Split Names

[dk]

How can we split the last names to another cell ?
Als if we have Mr & mrs john doe mike how can we split the title ?

 

[FSt1]

hi
you might try using text to columns.
on the menu bar....
Data>text to columns...>follow the wizzard.

If your have a lot of names of varing lengths, with and without titles, you
may have problems. you may need to group name of similar length and title so
that you can parce the data in blocks or groups. otherwise you may have to do
i one name at a time. also when parcing data, make sure you have enough blank
cells to the right of the data you are parcing because this function will
overwrite data to the right.

regards
FSt1

 

[Gord Dibben]

Data>Text to Columns would be sufficient for just splitting out a firstname and
lastname.

For more complex splitting see Chip Pearson's site.

http://www.cpearson.com/excel/FirstLast.htm

Note the downloadable workbook examples.


Gord Dibben MS Excel MVP

 

[FSt1]

hi again,
afterthought.
if this is the first time you are using this feature, make a copy of your
name list just in case.

Regards
FSt1

 

[dk]

This doen't seem to work because it's to complicated please just answer how
can we remove thw last word of whole column

 

[Ragdyer]

Here's a text formula.
With names starting in A1, enter this in B1:

=RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

And copy down as needed.

 

[Ragdyer]

The line wrap may present a problem.

Try this if you have trouble with the first formula:

=RIGHT(A1,LEN(A1)-FIND("^^^",SUBSTITUTE(A1," ","^^^",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

 

[Ron de Bruin]

See this add-in
It will insert a new column in your table with a formula for you

Look on the text tab
http://www.rondebruin.nl/datarefiner.htm

 

[Teethless mama]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Ragdyer]

Nice!