US20170337175A1 - Enhanced Find and Replace for Electronic Documents - Google Patents

Abstract

An enhanced find and replace feature for a spreadsheet program module is disclosed. The enhanced find and replace feature allows users to search for terms and attributes across multiple sheets of a workbook. The find and replace feature also allows searches to be performed based on the formatting attributes of a cell as well as the textual or numerical contents of a cell. The enhanced find and replace feature also allows users to perform bulk formatting of cells (even cells without text) in a spreadsheet. In another aspect, the enhanced find and replace feature lists all the results of the find operation in a results list so that the user may choose which result(s) to view and edit. In another aspect, the find and replace feature is modeless so that a user can search for terms or attributes, view a results list, exit the find and replace feature, perform editing functions in the document and then return to the find and replace feature. With modelessness, when the user clicks into a worksheet and clicks back into the find and replace dialog, the search resumes on the current selection in the worksheet.

Description

RELATED APPLICATIONS
• This application is a Continuation of co-pending U.S. application Ser. No. 13/524,183 entitled “Enhanced Find and Replace for Electronic Documents” filed Jun. 15, 2012, now issued as U.S. Pat. No. ______, which is a continuation of U.S. application Ser. No. 09/755,863 entitled “Enhanced Find and Replace for Electronic Documents” filed Jan. 5, 2001, now issued as U.S. Pat. No. 8,205,149, the disclosures of which are incorporated herein by reference.
TECHNICAL FIELD
• The invention generally relates to electronic spreadsheet program modules, and more particularly relates to an enhanced find and replace feature for electronic documents.
BACKGROUND
• The find and replace feature is commonly found in software application program modules to manage the complexities in medium to large documents and data stores. Although most every software application provides some find and replace capability, there is a need for a find and replace feature with ease of use, flexibility in searching, and utility of searching. For example, in most spreadsheet program modules, the find feature is limited to displaying one result at a time and the results of find and replace features are difficult to manage. For example, if the user is searching for a particular instance of a term, the user must navigate through multiple instances of the term to reach the particular instance desired.
• A problem with spreadsheet find and replace features is the inability to perform bulk formatting operations. Bulk formatting refers to performing find and replace operations on the formatting attributes of a cell rather than the textual or numerical contents of a cell. For example, bulk formatting may include such operations as deleting all red text, searching only secured sections of a document or searching for data types (such as currencies or numbers in scientific notation).
• Another drawback of spreadsheet find and replace features is their inability to search across multiple sheets unless each individual sheet is selected. Thus, a user may perform a find operation and expect that they are searching multiple sheets when, in fact, only the active sheet was searched.
• Still another drawback of spreadsheet find and replace features is the inability to perform find and replace operations on non-textual elements of cells. For example, changing all the percentages in a worksheet to decimals must be performed manually rather than via an automated find and replace feature.
SUMMARY OF THE INVENTION
• The present invention meets the above-described needs by providing an enhanced find and replace feature for a spreadsheet program module. The enhanced find and replace feature allows users to search for terms and attributes across multiple sheets of a workbook. The find and replace feature also allows searches to be performed based on the formatting attributes of a cell as well as the textual or numerical contents of a cell. The enhanced find and replace feature also allows users to perform bulk formatting of cells (even cells without text) in a spreadsheet. In another aspect, the enhanced find and replace feature lists all the results of the find operation in a results list so that the user may choose which result(s) to view and edit. In another aspect, the find and replace feature is modeless so that a user can search for terms or attributes, view a results list, exit the find and replace feature, perform editing functions in the document and then return to the find and replace feature. With modelessness, when the user clicks into a worksheet and clicks back into the find and replace dialog, the search resumes on the current selection in the worksheet.
• That the invention improves over the drawbacks of find and replace features and accomplishes the advantages described above will become apparent from the following detailed description of the exemplary embodiments and the appended drawings and claims.
BRIEF DESCRIPTION OF THE FIGURES
• FIG. 1 is a block diagram of a personal computer that provides the operating environment for an embodiment of the present invention.
• FIG. 2 is an illustration of the slim view of the find and replace dialog box in accordance with an embodiment of the present invention.
• FIG. 3 is an illustration of the expanded view of the find and replace dialog box in accordance with an embodiment of the present invention.
• FIG. 4 is an illustration of the format cells dialog box with the Number tab selected.
• FIG. 5 is an illustration of the format cells dialog box with the Alignment tab selected.
• FIG. 6 is an illustration of the format cells dialog box with the Font tab selected.
• FIG. 7 is an illustration of the format cells dialog box with the Border tab selected.
• FIG. 8 is an illustration of the format cells dialog box with the Patterns tab selected.
• FIG. 9 is an illustration of the format cells dialog box with the Protection tab selected.
• FIG. 10 is an illustration of the results list view of a find and replace dialog box in accordance with an embodiment of the present invention.
• FIG. 11 is an illustration of the expanded view of the find and replace dialog box with a dropdown menu showing previous search terms and formatting attributes.
• FIG. 12 is an illustration of the slim view of a find and replace dialog box with the replace tab selected.
• FIG. 13 is an illustration of the expanded view of a find and replace dialog box with the replace tab selected and the options button selected.
• FIG. 14 is a logic flow diagram of a method for performing the find all operation in accordance with an embodiment of the present invention.
• FIG. 15 is a logic flow diagram of a method for performing the replace all operation in accordance with an embodiment of the present invention.
• FIG. 16 is a logic flow diagram of a method for performing the choose format from cell operation in accordance with an embodiment of the present invention.
DETAILED DESCRIPTION OF EMBODIMENTS OF THE INVENTION
• An embodiment of the present invention will be incorporated into the “EXCEL 10” spreadsheet program module manufactured by Microsoft Corporation of Redmond, Wash. Briefly described, in one embodiment, the invention is an enhanced find and replace feature for the “EXCEL 10” spreadsheet program module. The enhanced find and replace feature allows users to search for terms and attributes across multiple sheets of a workbook. The find and replace feature also allows searches to be performed based on the formatting attributes of a cell and/or the textual/numerical contents of a cell. For example, a search may be performed for all cells with a yellow background or for all cells with decimals, just to name a few.
• The enhanced find and replace feature also allows users to perform bulk formatting of cells (even cells without text) in a spreadsheet. For example, a user can change all cells with a yellow background to cells with a red background. In another aspect, the enhanced find and replace feature lists all the results of the find operation in a results list so that the user may choose which result(s) to view and edit. In another aspect, the present invention provides an enhanced find and replace feature with modelessness so that a user can search for terms or attributes, view a results list, exit the find and replace feature, perform editing functions in the document and then return to the find and replace feature. With modelessness, when the user clicks into a worksheet and clicks back into the find and replace dialog, the search resumes on the current selection in the worksheet. In still another aspect of the invention, the find and replace feature dialog comprises a dropdown menu for viewing and selecting previous searches.
Exemplary Operating Environment
• FIG. 1 and the following discussion are intended to provide a brief, general description of a suitable computing environment in which the invention may be implemented. While the invention will be described in the general context of an application program that runs on an operating system in conjunction with a personal computer, those skilled in the art will recognize that the invention also may be implemented in combination with other program modules. Generally, program modules include routines, programs, components, data structures, etc. that perform particular tasks or implement particular abstract data types. Moreover, those skilled in the art will appreciate that the invention may be practiced with other computer system configurations, including hand-held devices, multiprocessor systems, microprocessor-based or programmable consumer electronics, minicomputers, mainframe computers, and the like. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote memory storage devices.
• With reference toFIG. 1, an exemplary system for implementing the invention includes a conventionalpersonal computer20, including aprocessing unit21, asystem memory22, and asystem bus23 that couples the system memory to theprocessing unit21. Thesystem memory22 includes read only memory (ROM)24 and random access memory (RAM)25. A basic input/output system26 (BIOS), containing the basic routines that help to transfer information between elements within thepersonal computer20, such as during start-up, is stored inROM24. Thepersonal computer20 further includes ahard disk drive27, amagnetic disk drive28, e.g., to read from or write to aremovable disk29, and anoptical disk drive30, e.g., for reading a CD-ROM disk31 or to read from or write to other optical media. Thehard disk drive27,magnetic disk drive28, andoptical disk drive30 are connected to thesystem bus23 by a harddisk drive interface32, a magneticdisk drive interface33, and anoptical drive interface34, respectively. The drives and their associated computer-readable media provide nonvolatile storage for thepersonal computer20. Although the description of computer-readable media above refers to a hard disk, a removable magnetic disk and a CD-ROM disk, it should be appreciated by those skilled in the art that other types of media which are readable by a computer, such as magnetic cassettes, flash memory cards, digital video disks, Bernoulli cartridges, and the like, may also be used in the exemplary operating environment.
• A number of program modules may be stored in the drives andRAM25, including anoperating system35, anapplication program module36, such as Microsoft's “EXCEL 10” spreadsheet program module,other program modules37, andprogram data38. Theapplication program module36 may include a find and replace engine (not shown) to implement an embodiment of the present invention. A user may enter commands and information into thepersonal computer20 through akeyboard40 and pointing device, such as amouse42. Other input devices (not shown) may include a microphone, joystick, game pad, satellite dish, scanner, or the like. These and other input devices are often connected to theprocessing unit21 through aserial port interface46 that is coupled to the system bus, but may be connected by other interfaces, such as a game port or a universal serial bus (USB). Amonitor47 or other type of display device is also connected to thesystem bus23 via an interface, such as avideo adapter48. In addition to the monitor, personal computers typically include other peripheral output devices (not shown), such as speakers or printers.
• Thepersonal computer20 may operate in a networked environment using logical connections to one or more remote computers, such as aremote computer49. Theremote computer49 may be a server, a router, a peer device or other common network node, and typically includes many or all of the elements described relative to thepersonal computer20, although only amemory storage device50 has been illustrated inFIG. 1. The logical connections depicted inFIG. 1 include a local area network (LAN)51 and a wide area network (WAN)52. Such networking environments are commonplace in offices, enterprise-wide computer networks, Intranets and the Internet.
• When used in a LAN networking environment, thepersonal computer20 is connected to theLAN51 through anetwork interface53. When used in a WAN networking environment, thepersonal computer20 typically includes amodem54 or other means for establishing communications over theWAN52, such as the Internet. Themodem54, which may be internal or external, is connected to thesystem bus23 via theserial port interface46. In a networked environment, program modules depicted relative to thepersonal computer20, or portions thereof, may be stored in the remote memory storage device. It will be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers may be used.
• FIG. 2 is aslim view200 of a find and replace dialog box in accordance with an embodiment of the present invention. Theslim view200 of the find and replace dialog box includes afind tab202 and a replacetab204. By using the mouse to navigate and click on the find tab and the replace tab, the user may navigate between the find feature and the replace feature. Theslim view200 of the find and replace dialog box further includes a findnext button206. When selected, the findnext button206 will find the next occurrence in the active spreadsheet worksheet of the text and/or formatting entered in the find whatfield207 and activate the next cell that matches the find criteria (text and/or formatting). The behavior associated with the findnext button206 is different than other find and replace dialogs because it allows the user to search based on formatting attributes.
• Theslim view200 of the find and replace dialog box further includes a find allbutton208. When selected the find allbutton208 causes the active spreadsheet worksheet and/or any other worksheets in the active workbook to be searched for all occurrences of the text entered in the find whatfield207. Which worksheet(s) is searched is controlled by the “within” dropdown in the expanded view. The default is to search within the worksheet. The results of the find all search are shown to the user in a results list (described in reference toFIG. 10).
• Theslim view200 of the find and replace dialog box further includes anoptions button210. When selected in theslim view200, theoptions button210 will expand theslim view200 to the expanded view300 (FIG. 3) so that formatting search options may be entered.
• The slim view of the find and replace dialog box further includes aclose button212 that closes the find and replace feature and adropdown button214. when selected,dropdown button214 lists previous searches entered into the find whatfield207 so that the user may quickly select a previous search term and then search for that term again.
• Referring now toFIG. 3, the expandedview300 of the find and replace dialog box will be described. As described above, when selected in theslim view200, theoptions button210 will expand theslim view200 to the expandedview300. Conversely, when selected in the expandedview300, theoptions button210 will reduce the expandedview300 to theslim view200.
• The expandedview300 includes aformat button302. When selected, theformat button302 causes a format cells dialog box400 (FIGS. 4-9) to be displayed. As will be described below, the format cells dialog box allows a user to enter the formatting attributes of the cell and text to be found or the formatting attributes of the cell and text to use as replacements.
• The expandedview300 further includes aformat preview304. After selecting a format to locate or replace with, a sample of the selected format will be displayed in theformat preview304. InFIG. 3, no format has been selected so the format preview displays “No Format Set”. Theformat preview304 provides the user with a quick visual indication of what formatting attributes will be found should the user select the find allbutton208 or the findnext button206. For formatting attributes such as Locked, Hidden or Merged Cells there's no expected preview behavior. Thus, for these attributes, a preferred embodiment of the present invention will add an asterisk to the preview text to illustrate that there are additional formatting attributes beyond those displayed. The formatting attributes that are clearly visible via the preview are font and patterns.
• The expandedview300 also includes aformat dropdown button306. When selected, the format dropdown button includes three options: “Format”, “Choose Format From Cell”, and “Clear Find Format”. When selected, the “Format” option opens the format cells dialog box (FIG. 4) with all options in a ninched state. A ninched state refers to a state that is neither selected nor deselected. For example, suppose you are searching for all cells with the text “Fido”. If you deselect blue backgrounds, then any cells with blue backgrounds will not be returned in the search (even if the cells include the text “Fido”). However, if you place the background color in a ninched state, then background color will be ignored in the search and the search will return all cells with the text “Fido” regardless of background color.
• When selected, theformat dropdown button306 also displays an option for “Choose Format From Cell”. When chosen, the “Choose Format From Cell” option will change the user's cursor into an eyedrop shaped cursor. The user may then move the eyedrop shaped cursor over a cell(s) and select a cell(s). The “Choose Format From Cell” option will determine the attributes of the selected cell(s), apply them to all tabs of the format cells dialog, and enter these attributes into theformat preview304. Thus, a user may use the “Choose Format From Cell” option to simply and quickly search for cells that have the same attributes as a selected cell(s). If multiple cells are selected, the formatting attributes that the cells have in is common are used and the other formatting attributes are ninched. For example, if two cells are shaded red but one has a date and the other has a currency, then the search would be for red cells while ignoring the number formatting attribute.
• When selected, theformat dropdown button306 also displays an option for clearing all formats known as the “Clear Find Format” option. This option, when selected, restores all tabs to ninched states. The “Clear Find Format” option is greyed out if no formatting attributes are selected.
• The expandedview300 also includes a withindropdown menu308, a searchdropdown menu310 and a look indropdown menu312. The within dropdown menu allows a user to select a search within a worksheet or within an entire workbook. Thus, the present invention allows a user to search within an entire workbook.
• The searchdropdown menu310 allows a user to choose to search by rows or by columns. The look indropdown menu312 allows a user to choose to search in formulas, values or comments. The look in dropdown menu is also visible in the replace tab (although the user can only search in formulas in the replace tab). When searching on comments, the formatting options will be greyed out.
Searching for Non-Textual Elements
• As described briefly above, in a preferred embodiment of the present invention, the find and replace dialog box is able to search a spreadsheet for both textual and non-textual elements. These non-textual elements are referred to as formatting attributes and may be arranged into the following categories: number, alignment, font, border, patterns and protection.
• However, because of ninched state limitations, a user will not be able to search for items using “not”, such as not m/d/yy or not blue fill color.
• Referring now toFIG. 4, the formatcells dialog box400 is illustrated with theNumber tab402 selected. With the Number tab selected, the formatcells dialog box400 is used to set the number formatting for the find or replace feature. In addition to theNumber tab402, the format cells dialog box comprises anAlignment tab404, aFont tab406, aBorder tab408, aPatterns tab410, and aProtection tab412.
• TheNumber tab402 comprises acategory list414 with a plurality of number types that may be selected such as General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special and Custom. The user may select one of the categories in the category list to search for this type of number or to replace a cell(s) with this type of number. For example, a user may select to find all cells that are fractions and change them to decimals.
• If the user has selected a number type, then a sample of the number type is displayed in thenumber type preview416. For example, if the user chooses the decimal number type, then “0.999” (or another decimal number) may be displayed in thenumber type preview416.
• TheNumber tab402 also includes aclear button418. When selected, the clear button returns any previously selected formatting number attributes to the ninched state.
• The formatcells dialog box400 also includes a Choose Format fromCell button420. When selected, the Choose Format fromCell button420 functions as the “Choose Format From Cell” option and it will change the user's cursor into an eyedrop shaped cursor. The user may then move the eyedrop shaped cursor over a cell(s) and select a cell(s). The attributes of the selected cell(s) will be determined and applied to all tabs of the format cells dialog. Of course, the cell may also be selected before clicking the Choose Format fromCell button420.
• The formatcells dialog box400 also includes anOK button422 for accepting the entered formatting attributes and a Cancelbutton424 for exiting the format cells dialog box.
• Referring now toFIG. 5, the formatcells dialog box400 is illustrated with theAlignment tab404 selected. With the Alignment tab selected, the formatcells dialog box400 is used to set the alignment formatting for the find or replace feature. For example, using the Alignment tab, a user may select the text alignment, text control and orientation of the text to be searched for in the worksheet.
• Referring now toFIG. 6, the formatcells dialog box400 is illustrated with theFont tab406 selected. With the Font tab selected, the formatcells dialog box400 is used to set the font formatting for the find or replace feature. For example, using the Font tab, a user may select the font, font style, size, underlining characteristics, color and effects of the text to be searched for in the worksheet.
• TheFont tab406 includes afont list602 and afont selection604. A font type may be selected from thefont list602 and displayed in thefont selection604. TheFont tab406 further includes afont style list606 and afont style selection608. A font style (such as regular, italics, bold, bold italic) may be selected from thefont style list606 and the font style selection will be displayed infont style preview608.
• TheFont tab406 further includes afont size list610 and afont size selection612. A font size may be selected from thefont size list610 and the font size selection will be displayed infont size preview612.
• TheFont tab406 also includes anunderline dropdown menu614 in which the user can choose whether or not the text to be searched for/replaced with should be underlined.
• TheFont tab406 also includes a colordropdown menu616 in which the user can choose the color of the text to be searched for/replaced with.
• TheFont tab406 also includeseffects checkboxes618. These checkboxes are for strikethrough, superscript and subscript and may be ninched. These checkboxes select the effects that the user wants to find/replace with.
• TheFont tab406 also includes apreview window620 in which the user can preview the find/replace font attributes selected.
• Referring now toFIG. 7, the formatcells dialog box400 is illustrated with theBorder tab408 selected. With the Border tab selected, the formatcells dialog box400 is used to set the border formatting for the find and replace feature. For example, using the Border tab, a user may select the borders (including line style and line color) of the cells to be searched for in the worksheet.
• Referring now toFIG. 8, the formatcells dialog box400 is illustrated with thePatterns tab408 selected. With the Patterns tab selected, the formatcells dialog box400 is used to set the patterns formatting for the find and replace feature. For example, using the Patterns tab, a user may select the color and pattern of the cells to be searched for in the worksheet. The user may also view a preview window in which the user can preview the find/replace cell patterns attributes selected.
• Referring now toFIG. 9, the formatcells dialog box400 is illustrated with theProtection tab412 selected. With the Protection tab selected, the formatcells dialog box400 is used to set the protection formatting for the find or replace feature. In a preferred embodiment, the present invention allows find and replace operations to be performed on locked and hidden attributes. For example, formulas in an expense report may be locked and hidden even though a user is allowed to enter values into unlocked cells. Using prior find and replace features, these formulas were difficult to find and replace because find operations and replace operations had to be performed manually on locked and hidden formulas. There was no way to search on locked and hidden attributes. In one embodiment, the present invention allows find and replace operations on locked and hidden values.
• FIG. 10 is aresults list view1000 of a find and replace dialog box in accordance with an embodiment of the present invention. The results listview1000 includes aresults list1002 that provides an overview of all of the places within the work sheet or workbook where a particular piece of text and/or formatting is found. Individual cells matching the requested text and/or formatting attributes are found and listed in theresults list1002. Each identified cell includes a workbook name in abook column1004 and a worksheet name in asheet column1006. The results list also includes aname column1008 which shows the defined name of a cell, if available. The name column contains defined names for cells. For example, a cell may be named interest rate, while another cell may be named principal.
• The results list includes acell column1010. Listed in the cell column is the cell field of the identified cell(s). The cell column supports RICI references as well as standard references. The contents of the identified cell(s) that are part of the find results (the value the user sees for the cell) are listed in thevalue column1012. The results list also includes aformula column1014 listing the formulas of the identified cell(s), if applicable.
• It should be noted that the entries under the columns of the results list1002 are links and when a user clicks on an entry in the results list, the application program module moves the display to the selected cell reference. In an embodiment of the invention, multiple cell selection is also possible. If a user selects multiple entries in the results list, those cells will be selected in the sheet. The user could navigate between these cells using the tab key. Clicking the replace tab with multiple selected cells would apply the selected replacement text and/or formatting to the selected cells. Canceling out of the dialog will retain the user's selection choices.
• Edits to individual cells may be made using the results list. For example, typing while a results list entry is selected will populate that cell with the typed characters. While active, the results list is updated as edits to the spreadsheet are made.
• It should be understood that the results list is obtained when the user selects the Find Allbutton208 rather than theFind Next button206. The results list is generated fresh for every search and is intended for use within the find and replace dialog. If the Find Next button is selected after a results list has been displayed, the results list continues to be displayed.
• After the find all button is selected and the results list is generated, the first entry in the results list is automatically selected (along with the corresponding cell on the worksheet).
• The headers of the results list columns may be dragged to change the size of the columns and clicking on an individual header will sort the list by that item.
• If the user selects found cells that span across sheets, the invention will disable the selecting of cells on another sheet.
• It should be understood from the foregoing description that the results list for a find operation is not limited to uncovering one match at a time. The results list may present a descriptive, up-to-date list to inform the user of all occurrences of the search term. The find dialog will identify cells and list them with the workbook name, worksheet name and cell reference. The dialog will also display the contents of the cells that are part of the find results.
• Referring now toFIG. 11, the expandedview300 of the find and replace dialog box is illustrated with a dropdown menu showing previous search terms and formatting attributes. Because users often need searches to be repeated within a session, thedropdown button214, when selected, will list up to 5 previous searches. Typically, the searches will be retained only for the session and the dropdown will not be workbook specific.
• The following table lists the case and behavior for retaining the previous search list in accordance with an embodiment of the present invention:

• 	Case	Behavior
  	Reboot application	List cleared
  	File close	List retained, unmodified
  	File new	List retained, unmodified
  	Switch books	List retained, unmodified

• The list of previous searches will be organized chronologically. Duplicate entries of a search will not appear even if the search was performed multiple times.
• When an entry from the dropdown is selected, the formatting attributes will be changed to reflect their state at the time that the last search was run. The other settings will not reflect the old state.
• Referring now toFIG. 12, theslim view1200 of a find and replace dialog box is illustrated in accordance with an embodiment of the present invention. InFIG. 12, the replacetab204 has been selected. The display in association with the replacetab204 is similar to the display in association with the find tab (FIG. 2) and similar numbering is used for similar elements. It should be understood that the format cells dialog box (FIGS. 4-9) operates similarly for the replace function as it does for the find function. However, when used with the replace function, the formatting attributes are used to indicate with what formatting attributes to replace the found cells with.
• In addition to the elements described in association withFIG. 2, the replace tab also comprises a replace withfield1202. The replace with field is used to enter text to replace the text in the find what field. The replacebutton1206 will replace the active cell (if it matches the text and/or formatting entered in the find what field) with the text and/or formatting entered in the replace withfield1202. The replace allbutton1204 will find all occurrences in the active spreadsheet worksheet of the text and/or formatting entered in the find whatfield207 and replace them with the text and/or formatting entered in the replace withfield1202.
Replace Behavior
• The replace all and replace buttons will appear when the replace tab is clicked on or when Edit_Replace is chosen from the menus. The fields on the replace tab are synchronized with the Find tab. For example, if a user enters “hello” in the Find What field of the Find tab, “hello” will be populated into the Find what field on the replace tab. The same applies for the other options available when the dialog is in the slim view (with the one exception that formulas is the only option in the Look in listbox in the replace tab).
• The following rules apply to replace behavior in a preferred embodiment of the present invention. When formatting attributes have been entered for the replace option but no replace text has been entered, then the replace function replaces format but not text. When formatting attributes have not been entered for the replace option but replace text has been entered, the replace function replaces text but not format. When both formatting attributes and replace text have been entered for the replace option, both text and format are replaced.
• Referring now toFIG. 13, the expandedview1300 of the find and replace dialog box will be described with the replace tab selected and the options button selected. When selected in theslim view1200, theoptions button210 will expand theslim view1200 to the expandedview1300. Conversely, when selected in the expandedview1300, theoptions button210 will reduce the expandedview1300 to theslim view1200.
• The expandedview1300 includes aformat button1208. When selected, theformat button1208 causes a format cells dialog box400 (FIGS. 4-9) to be displayed as was described above for the find operation. The format cells dialog box allows a user to enter the text and formatting attributes of the cell that is to replace the found text and formatting attributes.
• The expandedview1300 further includes aformat preview1212. After selecting a format to replace the located format, a sample of the replace format will be displayed in theformat preview1212. InFIG. 13, no format has been selected so the format preview displays “No Format Set”. Theformat preview1212 provides the user with a quick visual indication of what attributes will be replaced should the user select the replace allbutton1204 or the replacebutton1206. For formatting attributes such as Locked, Hidden or Merged Cells there's no expected preview behavior. Thus, for these attributes, a preferred embodiment of the present invention will add an asterisk to the preview text to illustrate that there are additional formatting items beyond those displayed. The formatting attributes that are clearly visible via the preview are font and patterns.
• The expandedview1300 also includes aformat dropdown button1210. When selected, the format dropdown button comprises three options: “Format”, “Choose Format From Cell”, and “Clear Replace Format”. When selected, the “Format” option opens the format cells dialog box (FIG. 4) with all options in a ninched state.
• When selected, theformat dropdown button1210 also displays an option for “Choose Format From Cell”. When selected, the “Choose Format From Cell” option will change the user's cursor into an eyedrop shaped cursor. The user may then move the eyedrop shaped cursor over a cell(s) and select a cell(s). The “Choose Format From Cell” option will determine the attributes of the selected cell(s), apply them to all tabs of the format cells dialog, and enter these attributes into the replaceformat preview1212. Thus, a user may use the “Choose Format From Cell” option to simply and quickly change a cell's attributes to those of a selected cell(s).
• When selected, theformat dropdown button1210 also displays an option for clearing all formats known as the “Clear Replace Format” option. This option, when selected, restores all tabs to all ninched states. The “Clear Replace Format” option is greyed out if no replace format is selected.
Modelessness
• It should be understood that the preferred embodiment of the invention operates modelessly. Modelessness refers to the ability of a user to edit a worksheet while the find and replace dialog is active. In other words, a user can switch back and forth between the find and replace dialog and the active worksheet. After a user selects and edits a worksheet, the user may click back into the find and replace dialog causing the search operation to resume on the current selection.
• For example, suppose a user wants to find every instance of the word “GNP”. The user selects cell Al and enters “GNP” in the find whatfield207. The user selects the find next button and is taken to cell B33. The user notices an unrelated spelling error, clicks on the worksheet and changes cell B25 to the acronym AGNPU. The user clicks on cell B2 and clicks the find next button. At this point, the spreadsheet program module resumes the find operation from the last selection (B2) and moves the selection to B25.
• If the user switches workbooks while the find and replace dialog box is still up, the find operation will resume on the newly selected book. Using the example described above, suppose that the cell changed to AGNPU is on a different book, then after entering the change, the search will resume on that book.
• In the case where the user selects a range of cells, the preferred embodiment of the present invention searches that range of cells only. If, after selecting a range of cells, then the user clicks elsewhere on the worksheet while the find and replace dialog box is up, the selection of the range of cells is broken and the find operation then encompasses the full worksheet rather than the originally selected range.
Moveaway
• In a preferred embodiment of the present invention, the find and replace dialog box should not obscure the active cell. Thus, the preferred embodiment of the present invention determines the location of the active cell on screen and positions the find and replace dialog box so that it does not obscure the active cell. The moveaway feature is important because of modelessness and the ability of the user to click into and edit cells without closing the find and replace dialog box.
Domain of Searching
• In a preferred embodiment of the present invention, the find and replace dialog box maybe used to search across many elements in a single step. Users may search the current sheet, the current selection, or the current workbook.
• In text finding, the find and replace dialog box will be able to search for cell values (numeric and non-numeric), formulas, and comments. Searching on cell values or formulas does not limit the search to only the cells that contain values (or formulas). Instead, cell values and formulas are designations that indicate when the dialog box searches a cell that contains a formula, whether the dialog box searches the value of the cell or the cell's underlying formula. For example, a cell may visually appear on the worksheet as “hello”, but actually contain the formula=“h” & “ello”. If the user was searching for the word hello, the user would need to search cell values and not formulas to locate the cell.
• Formatting without Text
• In a preferred embodiment, the present invention allows for finding and replacing non-textual elements of spreadsheet cells. For example, suppose a user wants to change all the cells with a blue background to a light yellow so that the spreadsheet prints better. From the edit menu, the user would choose the replace tab. The user would then click the format button to the right of the find textbox. The user would then select blue cell shading in the format cells/find dialog. The user would click the format button to the right of the replace textbox. The user would select light yellow cell shading in the format cells/find dialog and click the Replace All button. This operation replaces all the blue shading with light yellow.
• There's a logical gap, however, in how the find and replace dialog box operates with no text entered in the find field. If you bring up the find dialog and just click find next with nothing entered in the find field and no formatting attributes selected, the find and replace dialog doesn't find every blank cell. Instead, no cells are returned. The following table lists the resulting behavior for various combinations of text and formatting attributes:

• Find what:	Format?	Result
  Blank	Yes	finds all cells that have selected format
  Not blank	No	finds all cells with specified text
  Blank	No	finds nothing
  Not blank	Yes	finds all cells with specified text and format

• It should be understood that the options for the look in dropdown menu will not make a difference when there is no text in the Find What box.
Method for Find All Operation
• Those skilled in the art will appreciate that the logic flow diagrams ofFIGS. 14-16 are executed by the processing unit21 (FIG. 1) in response to instructions that have been incorporated into theapplication program module36.
• Referring now toFIG. 14, a logic flow diagram of amethod1400 for performing the find all operation in accordance with an embodiment of the present invention will be described.
• Themethod1400 begins atstart step1405 and proceeds todecision step1410 when the find all operation is requested (such as when the user selects the find all button208). Atdecision step1410, it is determined the whether there is text in the find whatfield207. If so, then the method proceeds todecision step1420. If not, then the method proceeds todecision step1415.
• Atdecision step1415, it is determined whether any formatting attributes have been entered to search for. If not, then the method proceeds to step1425 where the find operation is canceled and the method ends atstep1499.
• However, if atdecision step1415, it is determined there are formatting attributes entered to search for then the method proceeds to step1430.
• Atstep1430, all the cells with the specified formatting are located and the method proceeds to step1490.
• Atstep1490, the located cells are displayed in a results list and then the method ends atstep1499.
• Returning todecision step1410, if it is determined that there is text in the find what field, the method proceeds todecision step1420.
• Atdecision step1420, it is determined whether any formatting attributes have been entered to search for. If so, then the method proceeds to step1435. If not, then the method proceeds to step1440.
• Atstep1435, all the cells with the specified formatting and specified text are located and the method proceeds to step1490.
• Atstep1490, the located cells are displayed in a results list and then the method ends atstep1499.
• Atstep1440, all the cells with the specified text are located and the method proceeds to step1490.
• Atstep1490, the located cells are displayed in a results list and then the method ends atstep1499.
Method for Replace All Operation
• Referring now toFIG. 15, a logic flow diagram of amethod1500 for performing the replace all operation in accordance with an embodiment of the present invention will be described.
• Themethod1500 begins atstart step1505 and proceeds todecision step1510 when the replace all operation is requested (such as when the user selects the replace all button1204). Atdecision step1510, it is determined the whether there is text in the replace withfield1202. If so, then the method proceeds todecision step1520. If not, then the method proceeds todecision step1515.
• Atdecision step1515, it is determined whether any formatting attributes have been entered to replace with. If not, then the method proceeds to step1525 where the find and replace operation is canceled and the method ends atstep1599.
• However, if atdecision step1515, it is determined there are formatting attributes entered to search for then the method proceeds to step1530.
• Atstep1530, all the cells with the specified text and specified formatting are located and the method proceeds to step1535.
• Atstep1535, the formatting of the located cells is replaced with the replacement formatting and the method ends atstep1599.
• Returning todecision step1510, if it is determined that there is text in the replace with field, the method proceeds todecision step1520.
• Atdecision step1520, it is determined whether any formatting attributes have been entered to replace with. If so, then the method proceeds to step1537. If not, then the method proceeds to step1540.
• Atstep1537, all the cells with the specified formatting and specified text are located and the method proceeds to step1545.
• Atstep1545, the text and formatting attributes of the located cells are replaced with the replacement text and formatting attributes and the method ends atstep1599.
• Atstep1540, all the cells with the specified formatting and specified text are located and the method proceeds to step1550.
• Atstep1550, the text of the located cells is replaced with the replacement text and the method ends atstep1599.
• Method for Choose Format from Cell Operation
• Referring now toFIG. 16, a logic flow diagram of amethod1600 for performing the choose format from cell operation in accordance with an embodiment of the present invention will be described.
• Themethod1600 begins atstart step1605 and proceeds to step1610 where a selection of a Choose Format from Cell option is made, such as when the user selects the Choose Format fromCell button420. The method then proceeds to step1615.
• Atstep1615, the active cell in the worksheet or workbook is determined. The method then proceeds to step1620 where the formatting attributes of the active cell are determined. The method then proceeds to step1625.
• Atstep1625, the formatcell dialog box400 is populated with the formatting attributes from the selected (activated) cell. The method the ends atstep1699.
Search Direction
• The user may have the choice of searching by row (search moves from top to bottom) or by column (left to right except BiDi). Bidi refers to Bi-Directional language versions such as Hebrew and Arabic in which Latin text is handled left to right but Arabic is handled right to left. After that, the search proceeds in the following hierarchy:
• 1. current sheet
  2. current book (next sheet, being the sheet in the tab to the right of the active sheet).
• It will be understood from the foregoing description that a preferred embodiment of the present invention provides an enhanced find and replace feature that provides cross-sheet, format finding, a results list, modelessness and a goto/selection model for locating and changing data in bulk.
• From the foregoing description, numerous examples of embodiments and uses for the present invention may be understood by those skilled in the art.
• One example of a use of the preferred embodiment of the present invention follows. Suppose a management consultant is reviewing a company's internal financial reports. The data is spread across 7 different worksheets (by department and by statement, i.e., balance sheet and cash flows are each in separate workbooks). The consultant wants to find all places in the company that reported net losses. His first step is to search for all red, negative numbers such as (23,000). Eventually, he needs to create an itemized list of all the departments that are supposed to be profitable but instead are cost centers. He needs to be able to search across these different worksheets and find data that suits his criteria. Using the present invention, the management consultant is able to easily search for all red, negative numbers.
• A goto/selection example in accordance with an embodiment of the present invention follows. Suppose a user, a data entry clerk compensated on a per project basis, is asked to revise 1998 production figures with 1999 production figures that were estimated by an external firm and delivered on paper. The analysts that he's working for have highlighted in green all of the values he needs to replace. The user needs to select all the green cells and then use the tab key to jump between them to enter in data only in the appropriate cells. Thus, using an embodiment of the present invention, the data entry clerk's job is simplified because he can simply search for green cells and tab between them.
• It should be understood that although the foregoing description describes the invention as part of a spreadsheet program module the present invention may be part of any application program module that involves a find and replace dialog.
• It should also be understood that an embodiment of the present invention provides a cross-sheet find and replace feature, format finding (such as searching for all cells with a yellow background), a results list (listing each of the search results and updating the search results as cells are edited), cell attribute searches (searching for every cell with the same attributes as the selected cell) and a goto/selection model for locating and changing data in bulk. An embodiment of the present invention allows a combination of navigation and selection. The user may select one or multiple items from the results list and make revisions to the text and formatting attributes of the selected cell(s).
• It should be understood that, in a preferred embodiment, the find and replace invention supports the following objects: Border, Borders, Font, Interior, and Numberformat; the following properties: AddIndent, Hidden, HorizontalAlignment, IndentLevel, Locked, MergeCells, Orientation, ShrinktoFit, VerticalAlignment, and WrapText; and the following method: Clear.
• It should be understood that the foregoing pertains only to the preferred embodiments of the present invention, and that numerous changes may be made to the embodiments described herein without departing from the spirit and scope of the invention.

Claims (20)

What is claimed is:

1. A computer-implemented method for populating at least one field in a dialog of a spreadsheet program module, the method comprising:

editing at least one field in the dialog to correspond to at least one editable non-textual formatting characteristic of a cell in a spreadsheet; and

executing a find operation for identifying other cells in the spreadsheet with the at least one editable non-textual formatting characteristic.

2. The computer-implemented method ofclaim 1, further comprising determining the at least one editable non-textual formatting characteristic of the cell by receiving a selection of the cell containing the at least one editable non-textual formatting characteristic.

3. The computer-implemented method ofclaim 2, wherein editing the at least one field the dialog to correspond to at least one editable non-textual formatting characteristic of the cell comprises applying the at least one editable non-textual formatting characteristic to the at least one field in the dialog.

4. The computer-implemented method ofclaim 2, wherein receiving the selection of the cell comprises receiving an indication of a selection made by a user.

5. The computer-implemented method ofclaim 4, wherein receiving the indication of the selection made by the user comprises the user moving a cursor over the cell.

6. The computer-implemented method ofclaim 5, further comprising, in response to the user moving the cursor over the cell, modifying a visual representation of the cursor.

7. The computer-implemented method ofclaim 1, further comprising determining that there is no text in the at least one field in the dialog of the spreadsheet.

8. The computer-implemented method ofclaim 1, wherein executing the find operation for identifying other cells with the at least one editable non-textual formatting characteristic comprises identifying other cells with the at least one editable non-textual formatting characteristic and no text.

9. The computer-implemented method ofclaim 1, wherein executing the find operation for identifying other cells with the at least one editable non-textual formatting characteristic comprises identifying other cells with the at least one editable non-textual formatting characteristic and text.

10. A computer-implemented method for performing a replace operation on a file of a spreadsheet program module, the method comprising the following steps:

finding a cell in a spreadsheet with an editable non-textual formatting attribute in a find field of a find and replace dialog of a spreadsheet; and

replacing the editable non-textual formatting attribute of the found cell with another editable non-textual formatting attribute received in a replace field of the find and replace dialog for the replace operation.

11. The computer-implemented method ofclaim 10, further comprising receiving the editable non-textual formatting attribute in the replace field by determining the editable non-textual formatting attribute of a corresponding cell.

12. The computer-implemented method ofclaim 10, further comprising determining the editable non-textual formatting attribute of a corresponding cell by receiving a selection of the corresponding cell that contains the editable non-textual formatting attribute.

13. The computer-implemented method ofclaim 12, further comprising changing a shape of a cursor used to select the corresponding cell from a first shape to a second shape distinct from the first shape upon receiving the selection of the corresponding cell containing the at least one editable non-textual formatting attribute.

14. The computer-implemented method ofclaim 10, further comprising determining that there is no text in the replace field in the dialog of the spreadsheet.

replacing the editable non-textual formatting attribute of the found cell with another editable non-textual formatting attribute received in a replace field of the find and replace dialog for the replace operation.

15. The computer-implemented method ofclaim 10, wherein the found cell is without text.

16. A system for populating at least one field in a dialog of a spreadsheet program module, the system comprising:

a memory storage; and

a processing unit coupled to the memory storage, wherein the processing unit is operative to:

receive a selection of a formatted cell in the spreadsheet;

determine, in response to selecting the formatted cell, at least one editable non-textual formatting characteristic of the selected formatted cell; and

populate the determined at least one editable non-textual formatting characteristic to the at least one field in the dialog.

17. The system ofclaim 16, wherein the processing unit is further operative to apply the at least one editable non-textual formatting characteristic populated to the dialog to at least one other cell.

18. The system ofclaim 16, further comprising the processing unit being operative to execute a find operation for identifying cells with the populated at least one editable non-textual formatting characteristic.

19. The system ofclaim 16, further comprising the processing unit being operative to execute a find operation for identifying cells with the populated at least one editable non-textual formatting characteristic and no text.

20. The system ofclaim 16, further comprising the processing unit being operative to execute a find operation for identifying cells with the populated at least one editable non-textual formatting characteristic and text.

Images