Office Q&A: Mysterious symbols, file extensions, and filtering by partial strings
This month, we’ll review three simple solutions: a stubborn symbol, a Windows setting, and a partial filtering string. I’m using Excel 2013 on Windows 7. For your convenience, you can download the .xlsx or .xls demonstration file for the last topic. The other two solutions don’t require example files.
Mysterious symbol that won’t go away
Sue is working with a document created in 2010. It contains an outdated copyright symbol that she wants to delete, but she can’t select it. I suggested that the symbol was inserted into the document’s header, which sounds like a reasonable place to put it, especially if you want it to appear on every page.
Word doesn’t allow you to edit the document and the header or footer at the same time. To access the header or footer section, double-click the white space above or below the document, respectively. Only then can you edit the header and footer text and elements. In Word 2003, access these areas by choosing Header and Footer from the View menu. Figure A shows a header in edit mode. To the right you can see a logo, which appears to be in the document, but it’s actually in the header. To access it, you must open the header—then can you select it.
This header is in edit mode.
I’m confident that Sue knows how to access and edit a header or footer. It just didn’t occur to her that the copyright wasn’t in the body of the document. Once she opened the header, she was able to select and delete the symbol. The learning point here is this: If you can’t select the text or object, open the header or footer and try again.
Hide (or display) file extensions
Clinton uses the FILENAME field code to display the document’s filename, which includes the extension. After converting a Word document to the .pdf format, the .docx extention no longer makes sense. Clinton wants to inhibit or hide the extension.
Where file names and extensions are concerned, Word defaults to the system’s Windows setting. If Windows displays the extension, so will Word. That makes the solution easy. Simply change the Windows setting. That’s the good news. The bad news is that every Windows version has a different route to this setting! If you don’t find instructions for your version below, use your favorite search engine with the search string “how to hide extensions in Windows (fill in the blank with your version).”
- Double-click My Computer on the Desktop or choose My Computer from the Start menu.
- Choose Folder Options from the Tools menu.
- Check the Folder and search options.
- Click the View tab.
- Check or uncheck (accordingly), the Hide extensions for known file types.
- Click OK.
- Click the Start button and choose Control Panel.
- Click Appearance and Personalization.
- Click Folder Options.
- Click the View tab.
- Under Advanced settings, check or uncheck the Hide extensions for known file types option.
- Click OK.
Windows 8 and 10
- Open Windows Explorer.
- Click the View tab.
- Check or uncheck the File name extensions option.
Filter by partial strings
Rose is filtering a data set by ZIP code values, but she wants to include several regions in one search. For example, she might want to search for all 40xxx codes. US ZIP codes are numeric digits, even though we use them as text, and that complicates things. Let’s work through a simple example to see what happens:
- Click inside the data set.
- Click the Data tab, and then click Filter in the Sort & Filter group. Doing so will add filtering drop-downs to each column header in your data set. In Excel 2003, choose Filter from the Data menu and choose AutoFilter.
- Click the ZIP Code drop-down, and choose Number Filters. In Excel 2003, choose Custom Filter and skip to #5. Choose Custom Filter (Figure B).
- Choose Begins With from the first drop-down and enter 40 (Figure C).
- Click OK, and Excel will hide all of the records. Click Clear in the Sort & Filter group to view all the records.
That’s probably not what you expected, is it? The Filter feature doesn’t interpret numbers, even those formatted using the General format, as you might expect. There’s two ways to work around Excel’s (seemingly) misinterpretation of the data. First, you can enter a numeric filter using operators as follows:
- Repeat steps 1 through 4.
- From the first drop-down, choose is greater than or equal to and enter 40000.
- From the second drop-down, choose is less than and enter 41000 (Figure D).
- Click OK. This time, Excel understands the numeric-based filter strings and filters as expected (Figure E). Click Clear to display all of the records before continuing.
I mentioned a second way, and that involves a helper column that returns the text value of the ZIP Code as follows:
- Enter the following expression into cell D5 and copy it to the remaining cells (Figure F): =TEXT(C5,”00000″)
- Click inside the data set, and click Filter on the Data tab to remove the original filters.
- Click Filter a second time to include the helper column in the filtered set.
- Choose Text Filters from the new column’s drop-down.
- Choose Begins With (Figure G).
- Enter 40 (Figure H).
- Click OK (Figure I).
You might try applying the Text format to the ZIP Code values before applying any filter, but it doesn’t work. The filter still responds to the values as numbers.
Once you filter the data, you might want to copy the filtered records so you can work with them separately from the original data set. You can do so as follows:
- Select the range
- Press [F5], click Special at the bottom of the Go To dialog box, and then click Visible Cells Only (Figure J) to select only the visible cells in the selected range.
- Click OK to return to the sheet.
- Press [Ctrl]+[C] to copy the selected visible cells to the Clipboard.
- Select a target cell and paste the copied range by pressing [Ctrl]+[V].
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at [email protected]
Taken from –