parsename

[gh]

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

 

[Die_Another_Day]

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

 

[Bob Phillips]

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)

 

[Harald Staff]

Hi

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

HTH. Best wishes Harald

 

[Guest]

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]

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

 

[Die_Another_Day]

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