Convert Full Name to multiple cells
 

Hi, I want to get FirstName, LastName, and Title as separate cells. The
data is not all exactly the same:

Napolitano, Mr. Frank
Curtian, Chris

I guess I need some sort of test for Title (Mr. Mrs.) (looking for a period?)

TIA!

Convert Full Name to multiple cells
 

With functions in a module:

http://cjoint.com/?bvtxLDRI1z

Function LastName(c)
a = Split(c, ",")
LastName = a(0)
End Function

Function FirstName(c)
a = Split(c, ",")
P = InStr(a(1), ".")
If P = 0 Then
FirstName = Trim(a(1))
Else
FirstName = Trim(Mid(a(1), P + 1))
End If
End Function

Function Title(c)
a = Split(c, ",")
P = InStr(a(1), ".")
If P = 0 Then
Title = ""
Else
Title = Trim(Mid(a(1), 1, P - 1))
End If
End Function

JB
http://boisgontierjacques.free.fr



On 19 jan, 18:39, Karin wrote:
Hi, *I want to get FirstName, LastName, and Title as separate cells. *The
data is not all exactly the same:

Napolitano, Mr. Frank
Curtian, Chris

I guess I need some sort of test for Title (Mr. Mrs.) (looking for a period?)

TIA!

Convert Full Name to multiple cells
 

If your data is in column A, we will use column B for the title, column C for
the first name, column D for the lastname, and column E as a "helper" column.


In D1 enter:
=LEFT(A1,FIND(",",A1)-1) displays the lastname
In E1 enter:
=MID(A1,FIND(" ",A1)+1,255) displays title and first name
In B1 enter:
=IF(ISERROR(FIND(" ",E1)),"",LEFT(E1,FIND(" ",E1))) displays title (if its
there)
In C1 enter:
=IF(ISERROR(FIND(" ",E1)),E1,RIGHT(E1,FIND(" ",E1)+1)) displays firstname
--
Gary''s Student - gsnu200827


"Karin" wrote:

Hi, I want to get FirstName, LastName, and Title as separate cells. The
data is not all exactly the same:

Napolitano, Mr. Frank
Curtian, Chris

I guess I need some sort of test for Title (Mr. Mrs.) (looking for a period?)

TIA!

Convert Full Name to multiple cells
 

PERFECT! Thank you.

"Gary''s Student" wrote:

If your data is in column A, we will use column B for the title, column C for
the first name, column D for the lastname, and column E as a "helper" column.


In D1 enter:
=LEFT(A1,FIND(",",A1)-1) displays the lastname
In E1 enter:
=MID(A1,FIND(" ",A1)+1,255) displays title and first name
In B1 enter:
=IF(ISERROR(FIND(" ",E1)),"",LEFT(E1,FIND(" ",E1))) displays title (if its
there)
In C1 enter:
=IF(ISERROR(FIND(" ",E1)),E1,RIGHT(E1,FIND(" ",E1)+1)) displays firstname
--
Gary''s Student - gsnu200827


"Karin" wrote:

Hi, I want to get FirstName, LastName, and Title as separate cells. The
data is not all exactly the same:

Napolitano, Mr. Frank
Curtian, Chris

I guess I need some sort of test for Title (Mr. Mrs.) (looking for a period?)

TIA!