Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I seperate a full name field out into three seperate columns?
The name field is in the following format: Abbott, Susan H.
I would like to break it out into three seperate columns like below. Last Name First Name Middle Initial Abbott Susan H. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I seperate a full name field out into three seperate column
Assume:
Full name (Abbott, Susan H.) is in cell A2, First Name formula is in cell B2. Last Name formula is in cell C2. Middle formula Initial is in cell D2. B2 =LEFT(A2,FIND("~",SUBSTITUTE($A2," ","~",1))-2) C2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,MID($A2,FIND("~",SUBSTITUTE($A2," ","~",1))+1, FIND("~",SUBSTITUTE($A2," ","~",2))-FIND("~",SUBSTITUTE($A2," ","~",1))-1),RIGHT($A2,LEN($A2)-FIND("~",SUBSTITUTE($A2," ","~",1)))) D2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,RIGHT($A2,LEN($A2) - FIND("~",SUBSTITUTE($A2," ","~",2))),"") Watch the wrapping in this post. -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Rod" wrote: The name field is in the following format: Abbott, Susan H. I would like to break it out into three seperate columns like below. Last Name First Name Middle Initial Abbott Susan H. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I seperate a full name field out into three seperate columns?
=LEFT(A1,FIND(",",A1)-1)
=SUBSTITUTE(SUBSTITUTE(A1," "&D1,""),B1&", ","") =MID(A1,FIND("~",SUBSTITUTE(A1," ",",~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rod" wrote in message ups.com... The name field is in the following format: Abbott, Susan H. I would like to break it out into three seperate columns like below. Last Name First Name Middle Initial Abbott Susan H. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I seperate a full name field out into three seperate columns?
I tried the following with good success:
With full name in A2 B2 =MID(A2,1,FIND(", ",A2,1)-1) for last name C2 =MID(A2,FIND(" ",A2,1),FIND(" ",A2,1)-2) for first name D2 =RIGHT(A2,2) for middle initial - could be a problem if there is no middle initial Rod wrote: The name field is in the following format: Abbott, Susan H. I would like to break it out into three seperate columns like below. Last Name First Name Middle Initial Abbott Susan H. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a macro to hide certain columns | Excel Discussion (Misc queries) | |||
seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
Stop text from stringing into next field when empty | Excel Worksheet Functions | |||
Pivot Tables..I give up... | Excel Worksheet Functions | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions |