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

parsename
 

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

parsename
 

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" wrote in message
...
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

parsename
 

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" skrev i melding
...
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

parsename
 

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 a
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

parsename
 

This works in some cases, but not all the names have a middle initial.
They currently do not have any . after the middle initial and I would
like to add one to middle initial in column A as well.

TIA

ct60 wrote:

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 a
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

parsename
 

Try this macro:

Sub ChangeNames()
Dim lRow As Long
Dim cnt As Long
Dim strName As String
Dim strI As String 'String Initial
Dim lsPos As Long 'Last Space Position
Dim cPos As Long 'Comma Position
Dim lN As Long 'Length of strName
lRow = Range("A" & Rows.Count).End(xlUp).Row
For cnt = 1 To lRow
strName = Trim(Range("A" & cnt))
lsPos = InStrRev(strName, " ")
cPos = InStr(1, strName, ",")
lN = Len(strName)
strI = Right(strName, lN - lsPos)
If Len(strI) < 1 Then
'No Initial Found
Range("A" & cnt) = strName
Range("B" & cnt) = Right(strName, lN - cPos)
Else
'Initial Found
Range("A" & cnt) = strName & "."
Range("B" & cnt) = Mid(strName, cPos + 1, lsPos - cPos - 1)
Range("C" & cnt) = strI & "."
End If
Range("D" & cnt) = Left(strName, cPos - 1)
Next
End Sub

Charles

gh wrote:
This works in some cases, but not all the names have a middle initial.
They currently do not have any . after the middle initial and I would
like to add one to middle initial in column A as well.

TIA

ct60 wrote:

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 a
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