Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert number into full text amount | Excel Discussion (Misc queries) | |||
How do I convert number into full text amount | Excel Discussion (Misc queries) | |||
ASC() does not convert full-width to half-width letters (Exel 2003 | Excel Worksheet Functions | |||
SumIF full columns, multiple conditions | Excel Discussion (Misc queries) | |||
How to convert a hyperlink to its target full-text URL | Excel Discussion (Misc queries) |