Works: How to Separate a Combined Field into Individual Fields (134414)

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.

REFERENCES

For more information about this topic in Works for Windows 95, see your Works for Windows 95 printed documentation or online help.

Microsoft Works 3.0 for Windows User's Guide, Appendix A: "Text Functions" pages 529-530

For additional information, please see the following article in the Microsoft Knowledge Base:

109040 Works: How to Parse Data in a Spreadsheet or Database