Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Up Text in One Cell To Many
I need to split up names in one field to multiple fields. I am using the
extractelement function. My problem: some fields have a title and some don't. Therefore, I end up with the Title in the First name field and the first name in the Last name field. How do I write this to indicate tht if there is NO title to skip and move to the First name field? OR when there is no title, but a first name, middle initial and last name? I don't have consistancy in the naming fo the field I am extracting from. I might have: Bert T. Smith, Bert Smith, Mr. Bert Smith Thanks! -- Bert_Lady |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Up Text in One Cell To Many
On Thu, 8 Dec 2005 12:30:02 -0800, Bert_Lady
wrote: I need to split up names in one field to multiple fields. I am using the extractelement function. My problem: some fields have a title and some don't. Therefore, I end up with the Title in the First name field and the first name in the Last name field. How do I write this to indicate tht if there is NO title to skip and move to the First name field? OR when there is no title, but a first name, middle initial and last name? I don't have consistancy in the naming fo the field I am extracting from. I might have: Bert T. Smith, Bert Smith, Mr. Bert Smith Thanks! 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr 2. Use the following formulas: Title: I assumed that a "Title" starts at the beginning of the entry, and is terminated by a dot followed by a space. =REGEX.MID(A1,"^\w+\.") First Name: First Name is the first word that is followed by a space (no dot). =REGEX.MID(A1,"\w+\s") Last Name: Last Name is the last word in the string: =REGEX.MID(A1,"\w+$") =============================== Possible problems include names like: Mr. T. Bert Smith, Jr. This can be handled if necessary, but if it is going to be an issue, we would need more precise specifications. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Up Text in One Cell To Many
Thanks so much Ron. I did not do a very good job of expalining.
Just a bit more. The title can include, not only Mr. Mrs., but also Judge, Justice, etc. So not only titles ending with a period. -- Bert_Lady "Ron Rosenfeld" wrote: On Thu, 8 Dec 2005 12:30:02 -0800, Bert_Lady wrote: I need to split up names in one field to multiple fields. I am using the extractelement function. My problem: some fields have a title and some don't. Therefore, I end up with the Title in the First name field and the first name in the Last name field. How do I write this to indicate tht if there is NO title to skip and move to the First name field? OR when there is no title, but a first name, middle initial and last name? I don't have consistancy in the naming fo the field I am extracting from. I might have: Bert T. Smith, Bert Smith, Mr. Bert Smith Thanks! 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr 2. Use the following formulas: Title: I assumed that a "Title" starts at the beginning of the entry, and is terminated by a dot followed by a space. =REGEX.MID(A1,"^\w+\.") First Name: First Name is the first word that is followed by a space (no dot). =REGEX.MID(A1,"\w+\s") Last Name: Last Name is the last word in the string: =REGEX.MID(A1,"\w+$") =============================== Possible problems include names like: Mr. T. Bert Smith, Jr. This can be handled if necessary, but if it is going to be an issue, we would need more precise specifications. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Splitting Up Text in One Cell To Many
On Thu, 8 Dec 2005 15:50:02 -0800, Bert_Lady
wrote: Thanks so much Ron. I did not do a very good job of expalining. Just a bit more. The title can include, not only Mr. Mrs., but also Judge, Justice, etc. So not only titles ending with a period. -- You will need to incorporate a list of all possible titles in the first function. For example: =REGEX.MID(A1,"(Mr.)|(Mrs.)|(Ms.)|(Dr.)|(Judge)|(J ustice)") Note that the titles are enclosed in parentheses and separated by pipes. You can add to the list as you wish. The following is based on the full name being in A1, and the Title (if any) being in B1: For the First Name, we also make a modification in the formula so that instead of looking at the original string in A1, we process that string to remove the Title, and then take the first word after that. =REGEX.MID(SUBSTITUTE(A1,B1,""),"\w+\s") The Last Name formula remains unchanged: =REGEX.MID(A1,"\w+$") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text to appear at top of cell | New Users to Excel | |||
Wrap text ceases to function in Excel if cell exceeds 9 lines | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Text wider than one cell is not displayed in the next empty cell | Excel Discussion (Misc queries) | |||
Moving text from one cell to another. | Excel Worksheet Functions |