Changes in version 5.5 - December 2, 2014

Finished

On December 2, 2014 the new version 5.5 is released. Download

Update: Version 4 and 5

Your current license will work with all 4.x and 5.x versions.
All existing 4.x customers will get 5.x free of charge. You can use the same license details.

New features and improvements

• NEW: Range » Sort values inside each cell in selection...

  This tool sorts the contents of each cell in your selection.
  You can choose if the cell contents are:
  • Separated by a space
  • Separated by a comma
  • Separated by a comma + space
  • Separated by a line break (Alt+Enter)
  • Or enter your own separator
  You can sort either ascending (A-Z) or descending (Z-A)
  
  Tip: You can also use this on a (temporary) extra column in preparation to identify duplicate cell values that consist of multiple values.
  

• NEW: Information » Count duplicates and give each set of duplicates its own color

  This tool counts and colors the duplicate values in your selection.
  Each set of duplicated values is given its own unique color.
  
  This helps to quickly find the duplicated values, by having a set of colors each of which can readily be distinguished from one another.
  
  Example:
  If your selection contains 5 cells with the value "London" then those 5 cells will all get the same color, for example blue. And if the value "Paris" exists in 3 cells then those 3 cells will also get the same, but a different color, such as green.
  
  Remark:
  The utility is case sensitive which means for example that "Excel" and "EXCEL" are not the same.
  If your selection contains formulas then this utility compares the calculated values. This means that this utility considers a cell with the value 12 and a cell with the formula =3*4 as a duplicate.
  

• NEW: » Text » Change Case (with options)...

  This tool changes the text in the selected cells to uppercase, lowercase or other common capitalizations.
  You can change the text in selected cells to:
  • lowercase
  • UPPERCASE
  • tOGGLE cASE
  • Sentence case
    (first letter of each sentence in uppercase)
  • Glossary case
    (first letter of each sentence in lowercase)
  • Capitalize Each Word
    Optionally make the first letter of each word uppercase, and the rest of the word lower case.
    And if required you can specify the characters (in addition to a space) that indicate that a new word starts.
  
  Options:
  
  • Choose to preserve all capitalized text (useful in converting glossaries with abbreviations).
    
  • Set words to preserve/exclude.
  
  

• NEW: Range » Copy selection and paste into only the visible cells...

  This tool copies your current selection and then pastes the cells into only the visible cells in a given destination range.
  If there are hidden rows or columns in the target range, then those will be skipped. The data will then be pasted into the first next visible row or column.
  This tool can be useful when pasting data into a filtered list.
  Added by user request:
  "I want the ability to copy a sequential number of rows from one spreadsheet into another spreadsheet which is full of hidden rows, but I only want my rows to paste into the visible rows (without pasting into any hidden rows). Is there a way within ASAP?"
  

• NEW: Format » Replace conditional formatting with static formatting in selection

  This tool replaces the conditional formatting in the selected cells with regular (static) formatting.
  The formatting, that has been applied via conditional formatting rules (number, font, border and fill) is verified and then applied as regular (static) formatting.
  The conditional formatting rules are then removed, but not the effects.
  
  A practical example:  
  'We work a lot with conditional formatting, but our client wants us to "freeze" the format (which means no more use of conditional formatting...) in order to have a classical Excel format (mainly cell and font colors). The idea is to do the same thing as we can do with formulas or calculations "copy > paste as values" but for conditional formatting.'
  
  Note:
  This tool works in all Excel versions except for Excel 2007.
  Data Bars and Icon Sets cannot be made static with this tool.

• NEW: Right click menu

  The most popular tools added are added to the right-click/context menu for:
  • cells
  • column headings
  • row number
  • sheet tabs
  
  Sheets context menu with ASAP Utilities added:
  
  

• Improved: Export » Export selection or active sheet as new file...

  You can now choose Unicode for the (CSV) delimited text file format.
  

• Improved: Export » Export worksheets as separate files...

  New Unicode text file formats have been added:
  • CSV unicode  (Comma delimited) (*.csv)
  • Text unicode (Delimited by semicolon ';') (*.txt)
  • Text unicode (Pipe delimited) (*.txt)
  
  

• Improved: Text » Convert/recognize dates...

  Added the space+comma in the "convert to" options. This allows you to show a date as "May 21, 2014".

• Improved: File & System » Find and remove external links...

  Now has a button to report the found links:
  "Create a workbook with these results".
   
  

• Improved: Export » Export selected chart or range as image (file)...

  New: Export with increased size. You can set the scale for the image from 100% to 500%.
  

• Improved: Sheets » Split the selected range into multiple worksheets...

  You can now specify to use a certain worksheet as a source/template sheet instead of only a new blank worksheet.
  • First cell of destination range, you can specify the target address of where the data should be copied too (top-left cell of the target range).
  • Insert Rows, allows you to insert the data as new rows. Can be useful with templates.
  • Keep Source Formatting, copies everything, if you uncheck this option then it copies only the values to the new worksheets.
  
  Added by user request:
  "It would be great if you could expand a bit on the "Split Selected Range into Multiple Worksheets" feature. The feature works well right now, but I would love it if I could select a template sheet to be used when creating the new sheets (based off of the values in the specified column) instead of just splitting the worksheet into new plain blank worksheets. And with that, it would be useful if I could specify a
    cell or row range within the template in which I would like the data to be copied."

• Improved: Favorites & Shortcut keys » Edit your favorite tools and shortcut keys...

  Change of default shortcuts for the following tools:
  • Ctrl+Alt+K is replaced by: Ctrl+Alt+Right Arrow - Extend selection to the last used column
  • Ctrl+Alt+L is replaced by: Ctrl+Alt+Down Arrow - Extend selection to the last used row
  • Ctrl+Alt+V is replaced by: Ctrl+Shift+V  - Vision Control...
  When we started with ASAP Utilities in 1998 these 3 shortcuts/key combinations were not used by Excel.
  However, since Excel 2007, Excel uses the following shortcuts itself; Ctrl+Alt+L (Reapply Filter) and Ctrl+Alt+V (Paste Special dialog). Therefore we have changed these ASAP Utilities shortcuts to new ones.
  If you are upgrading then you need to press the "Reset to default values" button to have this changed, or you can manually make the changes if required:
  ASAP Utilities » ASAP Utilities Options » Edit your favorite tools and shortcut keys...
  

• Improved: function =ASAPSHEETNAME(optional reference)

  The function can now be given a range as parameter. That way you can get the name of another sheet instead of the current sheet. If omitted the name of the current sheet is returned.

• Improved: Columns & Rows » Autofit row height (also on merged cells)

  Now adjusts the row height based on the visible cells in the row only.
  Any hidden columns in your selection will now be skipped (In contrary to Excel's AutoFit which does not exclude hidden columns so the row height is then also based on the values in hidden columns).
  
  Compared to Excel's built-in "AutoFit Row Height", this tool:
  
  • works on merged cells too.
  • ignores data in hidden columns for determining the row height.

• Improved: Range » Find and/or replace in all sheets...

  Now allows a "Find" value of more than 255 characters.
  (Excel's built-in Find & Replace does not allow search strings of more than 255 characters)

• Improved: Bulk replace: replace multiple values at once

  via ASAP Utilities Options » New tools in development (English only)
  
  The layout has been simplified and your last used settings are now remembered. Furthermore, the range with these search and replace values will now always be excluded from the target range.
  You can specify a range with 2 columns with search and replace values, which allows you to do many replacements automatically after each other. The target can be a certain range, your current worksheet, all selected worksheets, the entire workbook or any other workbook that is opened.
  

• NEW: Detect adjacent row/column data and merge cells...

  via ASAP Utilities Options » New tools in development (English only)
  
  This tool detects adjacent cells with the same value and merges those cells into a single merged cell using Excel's merge.
  You can choose to:
  • merge cells vertically, per column, merge cells with the same value below it
  • merge cells horizontally, per row, merge cells with the same value next to it
  • set the alignment
  • add borders
  Useful for quick styling of tables for presentation purposes.
  

• New: Keep or delete certain Rich Text formatted characters...

  via ASAP Utilities Options » New tools in development (English only)
  
  This tool allows you to keep or delete the characters inside the cells in your selection that have a certain formatting applied (* Rich text formatting).
  You can choose to either keep or delete bold charters, italic characters, strikethrough characters, single underline characters, double underlined characters or normal characters within each cell in your selection.
  (* Rich text formatting is a combination of font, font size, font style (for example, bold or italic) and underlining formatting in a single cell.)
  

• New: Text to Columns based on in-cell formatting changes (split by bold, italic, color etc.)

  via ASAP Utilities Options » New tools in development (English only)
  
  This tool allows you to split the values inside the cells into separate columns, based on a change in the applied formatting (* Rich text formatting).
  It is a similar to Excel's Data > Text to Columns, however in this case the splitting is done based on changes in the applied formatting.
  For example, cell with the value "Drum [in bold] Tambour [normal] (f) [italic]" is then split into three columns; Bold, Tambour, (f)
  (* Rich text formatting is a combination of font, font size, font style (for example, bold or italic) and underlining formatting in a single cell.)
  

• NEW: Change cells to MAC Address formatting...

  via ASAP Utilities Options » New tools in development (English only)
  
  This will change the MAC addresses (such as 0123456789ab) in your selected cells into a more readable and standardized format.
  You can choose between the following styles:
  • 01:23:45:67:89:ab
  • 01-23-45-67-89-ab
  • 0123.4567.89ab
  The utility will if required automatically remove any spaces, hyphens (-), colons (:) or points(.) so that you can also easily convert it from one format to another.
  
  Added by user request:
  "It would be great if you had a way to format a cell (or column or row, or whatever) so that when you enter or paste in a MAC Address, that it would automagically reformat. For example, if I paste in 000c4231f617, it would auto-format to 00:0c:42:31:f6:17."
  
  Some technical background on what a MAC address is:
  A MAC (Media Access Control) address is a hardware identification number that uniquely identifies each device on a network.
  A MAC address, sometimes referred to as a hardware address or physical address, is a unique identifier that's assigned to a network adapter or any device with built-in networking capability, such as a printer. While an IP address can potentially be assigned to any device, a MAC address is "burned into" a device from the factory and therefore cannot be changed.
  If assigned by the manufacturer, a MAC address usually encodes the manufacturer's registered identification number and may be referred to as the burned-in address (BIA). It may also be known as an Ethernet hardware address (EHA), hardware address or physical address.
  The standard (IEEE 802) format for printing MAC-48 addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:), in transmission order (e.g. 01-23-45-67-89-ab   or   01:23:45:67:89:ab ). This form is also commonly used for EUI-64. Another convention used by networking equipment uses three groups of four hexadecimal digits separated by dots (.) (e.g. 0123.4567.89ab ). (http://en.wikipedia.org/wiki/MAC_address)

• General

  Speed improvements and a few small other improvements and spelling corrections.

Bug fixes

• Information » Create a list of filenames and properties in a folder...
  Improved handling of folder names that are longer than 255 characters.
• Sheets » Delete unused empty ending rows/columns
  Workaround for the error that occurred when the ending empty columns or rows were part of a pivot table.
• Sheets » Reset Excel's last cell
  Workaround for the error that occurred when after the last cell there were still empty columns and/or rows that were part of a pivot table.
• Columns & Rows » Delete all empty columns
  Returned an error when a pivot table contained empty columns because these could not be removed. These columns are now skipped.
• Columns & Rows » Delete all empty rows
  Returned an error when a pivot table contained empty row because these could not be removed. These rows are now skipped.
• Columns & Rows » Delete all hidden rows and/or columns...
  Returned an error when a pivot table contained hidden rows and/or columns because these could not be removed. These rows and/or columns are now skipped.
• Text » Convert/recognize dates...
  Now skips cells that contain only one or more space characters.
• Select » Select cells based on content, formatting and more...
  Improved the notification when no results were found or all selected cells match your criteria. Also improved preserving the initial selection when selecting values or formulas and no results were found.
• Favorites & Shortcut keys » Edit your favorite tools and shortcut keys...
  Your changes were not shown until you restarted Excel. This is now fixed. After closing the tool, you'll immediately see the changes in your "Favorites & Shortcut keys" menu again.
• Select » Select cells based on content, formatting and more...
  The options "contains" and "does not contain" were not case sensitive if you choose the option "Match Case". This is now fixed.
• General
  A few small bug fixes and improvements.