Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bert_Lady
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bert_Lady
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
text to appear at top of cell jenniss New Users to Excel 4 August 30th 05 02:08 AM
Wrap text ceases to function in Excel if cell exceeds 9 lines nycemail Excel Worksheet Functions 1 July 24th 05 06:09 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Text wider than one cell is not displayed in the next empty cell Len Pace Excel Discussion (Misc queries) 5 February 18th 05 01:24 AM
Moving text from one cell to another. estaban botas Excel Worksheet Functions 1 December 15th 04 03:04 PM


All times are GMT +1. The time now is 05:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"