Microsoft KB Archive/134414

= Works: How to Separate a Combined Field into Individual Fields =

Article ID: 134414

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Works 6.0
 * Microsoft Works 2000 Standard Edition
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.5 Standard Edition

-



This article was previously published under Q134414



SUMMARY
Sometimes, you may want to enter data into a single database field, rather than into separate fields. This can happen, for example, with first and last name information. When you enter data in this manner, it is not possible to sort on the individual parts of that data, for example, on the first name only. However, you can separate the data entered into one field by using multiple text string formulas.



MORE INFORMATION
In the following examples, full names are entered into a single database field called "Name." In the first example, the last name is entered first, followed by a comma, space, and then the first name. In the second example, the first name is entered first, followed by a space, and then the last name.

Example 1
If a field called "Name" contained data such as:

Smith, John

Adams, Kelly

Santos, Andy

You can derive the first name by entering this formula in a new field called "First Name":

=RIGHT(Name,(LENGTH(Name)-FIND(" ",Name,0)-1))

NOTE: There is one space between the quotation marks.

And in a new field called "Last Name", enter this formula:

=LEFT(Name,FIND(",",Name,0))

Example 2
If your field called "Name" contained the following data,

John Smith

Kelly Adams

Andy Santos

the first name can be pulled into a new field called "First Name" by entering:

=LEFT(Name,FIND(" ",Name,0))

NOTE: There is one space between the quotation marks.

And in a new field called "Last Name", type this formula:

=RIGHT(Name,(LENGTH(Name)-FIND(" ",Name,0)-1))

NOTE: There is one space between the quotation marks.

These formula work in the situations described above because they search for some sort of separator (for example, a comma or a space) as the place to divide the data. This means that if the data has been entered differently (for example, "Madonna" "Prince" "Joe Bob Briggs" or "Brown, Raymond L."), a lack of, or excess of separators can cause the formulas to produce errors or not correctly separate data.

