Prev: Use Find and Replace in Excel
Next: How do I show all words in a cell?
From: Kiley on 5 May 2010 11:46
I have a list of names in colum B, that I would like to split into column C
and D. The names in column B have a first name, middle intial and a last
name. I would like the last name to go into column c and the first name and
middle initial to go into column D.

Column B Column C Column D
Mary A Jones Jones Mary A
From: Mike H on 5 May 2010 12:08
Hi,

Put this in column C

=RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1," ","*",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))

Then this in column D

=SUBSTITUTE(B1,C1,"")

Drag both down
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Kiley" wrote:

> I have a list of names in colum B, that I would like to split into column C
> and D. The names in column B have a first name, middle intial and a last
> name. I would like the last name to go into column c and the first name and
> middle initial to go into column D.
>
> Column B Column C Column D
> Mary A Jones Jones Mary A
From: Bernard Liengme on 5 May 2010 12:15
To get "Jones": =MID(B1,FIND(" ",B1,FIND(" ",B1)+1)+1,LEN(B1))
to get "Mary A": =LEFT(B1,LEN(B1)-FIND(" ",B1)-1)
But these will not work if the middle initial is missing
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Kiley" <Kiley(a)discussions.microsoft.com> wrote in message
news:53DC9CD1-DEC8-46D7-AC58-B549AB7EC9F5(a)microsoft.com...
> I have a list of names in colum B, that I would like to split into column
> C
> and D. The names in column B have a first name, middle intial and a last
> name. I would like the last name to go into column c and the first name
> and
> middle initial to go into column D.
>
> Column B Column C Column D
> Mary A Jones Jones Mary A

From: Eduardo on 5 May 2010 12:17
Hi In column C enter

=LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1))

in column D

=RIGHT(A2,FIND(" ",A2))

"Kiley" wrote:

> I have a list of names in colum B, that I would like to split into column C
> and D. The names in column B have a first name, middle intial and a last
> name. I would like the last name to go into column c and the first name and
> middle initial to go into column D.
>
> Column B Column C Column D
> Mary A Jones Jones Mary A
From: Jacob Skaria on 5 May 2010 12:38
With name in cell A1
In cell B1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

In cell C1
=LEFT(A1,LEN(A1)-LEN(B1))

--
Jacob (MVP - Excel)


"Kiley" wrote:

> I have a list of names in colum B, that I would like to split into column C
> and D. The names in column B have a first name, middle intial and a last
> name. I would like the last name to go into column c and the first name and
> middle initial to go into column D.
>
> Column B Column C Column D
> Mary A Jones Jones Mary A
 |  Next  |  Last
Pages: 1 2
Prev: Use Find and Replace in Excel
Next: How do I show all words in a cell?