Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Formula for splitting names

2 views
Skip to first unread message

SNeilan

unread,
Feb 4, 2003, 7:47:15 PM2/4/03
to
If in one column I have names listed:
JOHN DOE
JAN R. SMITH
NANCY SMITH
TOM BLACK
ED BLACK

How do I write a formula that will split them into
separate rows: first name, middle initial, last name?

S

John Wilson

unread,
Feb 4, 2003, 9:21:10 PM2/4/03
to
S,

Chip Pearson has an excellent writeup on this.
Start here:
http://www.cpearson.com/excel/FirstLast.htm
and if you have any questions, post back.

John

adetaylor

unread,
Feb 5, 2003, 12:48:54 AM2/5/03
to
Column headings:

| Title | First_Name | Middle_Name | Last_Name |

Works with Mr,Mr.,mr, etc (or no titles). Works with
any amount of whitespace. Works with with or w/o
Title plus up to three names.

See:
http://makeashorterlink.com/?R23031953

Same link wrapped:
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-
8&newwindow=1&selm=ePNUtQDuCHA.616%40TK2MSFTNGP11


--Brian Taylor
Refinate (C)2001
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, and more.
www.adetaylor.com


>>"SNeilan" wrote:------------

adetaylor

unread,
Feb 5, 2003, 1:28:28 AM2/5/03
to
--Amendment to the referred to link--

Note:
There might be a suffice (Jr, Sr, II, III, etc) at
the end of the name without a middle name or middle
initial. If so, then, if it is preceded by the comma
in the cell, select all of the names and use Find &
Replace to replace the comma-space (", ") with just
a comma. Then it will be part of the last name for
sure. After you copy and Edit>PasteSpecial the
formulas to convert them to text, select all of the
last names and Find & Replace comma with comma-space.
Alternatively, Find & Replace " Jr" with "Jr;" to
merge it with the last name. After converting to
text, Find & Replace "Jr;" with " Jr" to unmerge.

--Brian Taylor
Refinate (C)2001
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, and more.
www.adetaylor.com


>>"adetaylor" wrote:----------


> Column headings:
> | Title | First_Name | Middle_Name | Last_Name |
> Works with Mr,Mr.,mr, etc (or no titles). Works with
> any amount of whitespace. Works with with or w/o
> Title plus up to three names.
> See:
> http://makeashorterlink.com/?R23031953
> Same link wrapped:
> http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-
> 8&oe=UTF-8&newwindow=1&selm=ePNUtQDuCHA.616%40TK2MSFTNGP11
>

0 new messages