parsename

I have a worksheet with the full name in one of the columns. Below is
an example. I would like to add a . after the middle initial then copy
the firstname, lastname and middle initial to seperate columns. Is ther
a function for this?

TIA

A B C D
LastName,John Q. John Q. LastName

You can't change column as that would be circular referencing. Try this
instead:
Column B = =IF(RIGHT(A1,1)=".",A1,A1 & ".")
Column C = =MID(B1,FIND(",",B1) + 1,LEN(B1) - FIND(",",B1) -3)
Column D = =RIGHT(B1,2)
Column E = =Left(B1,Find(",",B1) - 1)

Charles

gh wrote:
> I have a worksheet with the full name in one of the columns. Below is
> an example. I would like to add a . after the middle initial then copy
> the firstname, lastname and middle initial to seperate columns. Is ther
> a function for this?
>
> TIA
>
> A B C D
> LastName,John Q. John Q. LastName

B1: =MID(A1,FIND(",",A1)+1,FIND(" ",A1)-FIND(",",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,99)
D1: =LEFT(A1,FIND(",",A1)-1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"gh" <gh@at.net> wrote in message
news:O%234wm6ixGHA.4256@TK2MSFTNGP06.phx.gbl...
> I have a worksheet with the full name in one of the columns. Below is
> an example. I would like to add a . after the middle initial then copy
> the firstname, lastname and middle initial to seperate columns. Is ther
> a function for this?
>
> TIA
>
> A B C D
> LastName,John Q. John Q. LastName

Hi

Name parsing is pretty complicated. According to
http://www.dailydoseofexcel.com/arch...-with-outlook/
Outlook has the best algorithm for it among the Office tools. Give it a try.

HTH. Best wishes Harald

"gh" <gh@at.net> skrev i melding
news:O%234wm6ixGHA.4256@TK2MSFTNGP06.phx.gbl...
> I have a worksheet with the full name in one of the columns. Below is
> an example. I would like to add a . after the middle initial then copy
> the firstname, lastname and middle initial to seperate columns. Is ther
> a function for this?
>
> TIA
>
> A B C D
> LastName,John Q. John Q. LastName

Ok, this should do the trick. Assume that the name is in Cell A2.
Add cols as shown below. Make Col B (place where comma is) hidden. You can
actually avoid using a Col B, by substituting the formula in cell B2 whenever
you see B2 in the formula of the other cells.

Col A Col B Col C Col D Col E
Comma First Name Middle Name Last Name
LastName,John Q 9 John Q. LastName

So the formulas are:
Cell B2 (Comma): =FIND(",",A2)
Cell C2 (First Name): =MID(A2,B2+1,FIND(" ",A2, B2)-B2-1)
Cell D2 (Middle Name): =RIGHT(A2,1) & "."
Cell E2 (Last Name): =LEFT(A2,B2-1)

Then you can copy or autofill these formulas down each row.

Hope that helps.

Chris (ct60)


"gh" wrote:

> I have a worksheet with the full name in one of the columns. Below is
> an example. I would like to add a . after the middle initial then copy
> the firstname, lastname and middle initial to seperate columns. Is ther
> a function for this?
>
> TIA
>
> A B C D
> LastName,John Q. John Q. LastName
>