PRJ: Data with Spaces Treated as Separate Entry in AutoFilter (170630)



The information in this article applies to:

  • Microsoft Project 2000
  • Microsoft Project 98 for Windows

This article was previously published under Q170630

SYMPTOMS

In Microsoft Project, if you have spaces at either the beginning or the end of a text entry, the value will appear multiple times in the filter list when you use the AutoFilter feature. For example, if you have the following text entries in one field (column) in your project, and you click the AutoFilter arrow in the field that contains these values, all three values appear in the filter list:

ABC
<space>ABC
ABC<space>

where <space> is the character you get when you press the SPACEBAR once.

NOTE: Turn on AutoFilters by clicking the AutoFilter button on the Formatting toolbar.

CAUSE

In Microsoft Project, a leading or trailing space is considered a legal characters. Therefore, when a space is included at the beginning or end of a value, it is essentially part of the value.

WORKAROUND

To work around this problem, use either of the following methods:

Method 1: Remove the Spaces

Remove the leading and trailing spaces so that all values are equal, using these steps:

  1. On the Edit menu, click Replace.
  2. In the Find What box, type the value you want to find. For example, type <space>ABC.
  3. In the Replace box, type the value you want. For example, type ABC.
  4. In the Look In Field list, click the field name that contains the incorrect value.
  5. Click Replace All.
  6. Repeat steps 2 through 5 for each value you want to replace.

Method 2: Use the Custom AutoFilter

  1. Click the AutoFilter arrow in the field you want and then click Custom.
  2. In the Custom AutoFilter dialog box, in the operators list (on the left side of the dialog box), select Contains. In the values list (on the right side of the dialog box), type or select the value you want. For example, type ABC.
  3. Click OK.
The AutoFilter will ignore the spaces in the filter criteria, giving you a result set that contains all variations of ABC.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Project listed above.

Modification Type:MajorLast Reviewed:11/4/2003
Keywords:kbbug kbpending kbusage KB170630