Works: How to Separate a Combined Field into Individual Fields

Retired KB Content Disclaimer

View products that this article applies to.

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.

Back to the top

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.

Back to the top

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))

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/109040/EN-US/ ) Works: How to Parse Data in a Spreadsheet or Database

Back to the top

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

Back to the top

kbhowto kbui KB134414

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top