PATools XL Toolbox

since 1999

Data Toolkit

Help and Documentation


Tip: Use your browser's 'Find' (often Ctrl+F) to search within this page.


Getting started

We recommend you watch the video above to get started quickly.

To use the Data Toolkit, you have 3 tabs at the top to choose where to run, what to do, and options; then click 'Run'.

'Hide' temporarily hides the Toolkit so you can edit your data; next time you open it your selections are still there. 'Close' resets all selections to their defaults.



Where to run

To run the tools, you either

  • Run down columns to select 1 or more columns and where to stop in each, or
  • Run on range.

    The tool will stop before operating on any stop cell selected.

    The Data Toolkit cannot operate over multiple-areas eg C5:E11,H10:J21,L13:N22.



    What to do

    You have three sets of tools


  • 1 Cells and Rows where you specify what to do for cells that match criteria you set, or sets of tools for
  • 2 Numbers and Formulas, or
  • 3 Text

  • top



  • Screen off stops the screen updating whilst it runs, and so it can run faster
  • Ignore hidden will ignore any cell within a hidden row
  • Return to start will return to the start cell - otherwise the screen leaves you where it finishes running
  • Right-click any of the checkboxes to set the default.




    The following characters have special meanings:

    Character(s) Matches
    ? Any single character
    * Zero or more characters
    # Any single digit
    [charlist] Any single character in charlist
    [!charlist] Any single character not in charlist

    A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character and can include almost any characters in the ANSI character set, including digits. In fact, the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only if enclosed in brackets. The closing bracket ( ]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

    In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] results in a match if the corresponding character position contains any of the uppercase letters in the range A to Z. Multiple ranges can be included within the brackets without any delimiting. For example, [a-zA-Z0-9] matches any alphanumeric character.

    An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist is found. When used outside brackets, the exclamation mark matches itself.

    The hyphen (-) can be used either at the beginning (after an exclamation mark if used) or at the end of charlist to match itself. In any other location, the hyphen is used to identify a range of ANSI characters.

    When a range of characters is specified, they must appear in ascending sort order (A-Z or 0-100). So [A-J] is a valid pattern, but [J-A] is not.

    The character sequence [ ] is ignored since it is considered to be a zero-length string ("").



    Quick clix

    'Reset' resets the last used cell on a sheet - keeps your workbooks down in size and improves efficiency

    'Unhide all rows' and 'Unhide all columns' - right-click to run on all sheets

    'Remove hyperlinks' removes all hyperlinks on a sheet - right-click to run on all sheets

    'Protect / Unprotect' to protect (left-click) or unprotect (right-click) all worksheets in the workbook. NB To unprotect all worksheets that have passwords, the password must be the same on every sheet.



    back to top of page