how to with ASAP Utilities - ASAP Utilities Blog

There are few quick methods to remove the line breaks and carriage returns in cells. By default you might not know an easy and quick way to do this but it is fairly simple, either with just Excel, or with the help of ASAP Utilities. Let's say we have the following example. In this case the author has ...

ASAP Utilities how to with ASAP Utilities

How to: Create a pipe delimited file

Jan 15, 2010 Bastien 3 Comment

Last month I received the following question: I need to know how to convert an excel file into a pipe delimited file using the ASAP software. You can use the following tool to do this: ASAP Utilities » Export » Export selection or active sheet as new file... Then choose "CSV (comma separated value) or customer separated" and specify ...

ASAP Utilities how to with ASAP Utilities

How to: Insert a blank row after every 3rd row

Dec 31, 2009 Bastien 0 Comment

I just received the following question which might be interesting the share with you. ASAP Utilities contains many tools and one of the strengths is that you can often use a combination of the tools to accomplish what you need. The question I received this week: I want to insert a blank row after every 3rd row ...

ASAP Utilities how to with ASAP Utilities

Remove all empty rows – within your selection

Nov 9, 2009 Bastien 1 Comment

Last week I received the following question: Bastien, First, thank you so much for the best Excel add-on ever! Is there a way to remove empty rows in a only a selected section of the worksheet, not the whole worksheet? Or have I missed this option that is already included? Surely you can do this! The utility "Columns & Rows » ...

General How to how to with ASAP Utilities

Macro: Keep only ASCII characters 32-127

Aug 13, 2009 Bastien 3 Comment

Last week I received the following question: I get sent Excel files from various vendors. I have to convert these to SAS datasets that get moved to a UNIX system. The SAS datasets can only contain ASCII 32-127. So I need to remove these from the Excel file. You can use the "Text » Advanced character remove ...

ASAP Utilities how to with ASAP Utilities

How to: Remove all negative numbers

Aug 3, 2009 Bastien 17 Comment

Last week I received the following question: Is there an ASAP Utilities function that can remove all negative numbers from a spreadsheet? I haven't found it yet. Yes there is. You can use ASAP Utilities to help you to quickly delete negative numbers. You can use the following tool to select all cells with negative numbers (values ...

ASAP Utilities how to with ASAP Utilities

Identify duplicates

Jul 29, 2009 Bastien 0 Comment

I just received the following question: I will like to know how I can see the duplicates in a selection data? I'm aware I can delete duplicates, however I will like to know how to identify them first before deleted. Here's how to do this: You can use the following tool to select the cells with duplicated values: ...

ASAP Utilities how to with ASAP Utilities

Video: How to move or resize the selected range in Excel with ASAP Utilities

May 5, 2009 Bastien 8 Comment

The following video will show you how to how to move or resize the selected range in Excel by using ASAP Utilities. With this utility you can easily move or resize your selected range of cells. This utility will only change you selected range of cells only, not the data underneath it. Note: This has in 2021 be ...

ASAP Utilities how to with ASAP Utilities

How to: Quickly find the utility you need

Feb 10, 2009 Bastien 0 Comment

With the new version 4.3 of ASAP Utilities it will be easier to find the utility you need with the tool "Find and run a utility...". We have improved this already existing utility. The results are presented better and you can now immediately start/run the utility from the search results. If you click on the ...

ASAP Utilities how to with ASAP Utilities

How to: Remove all spaces, dashes and other garbage from numbers

Feb 10, 2009 Bastien 0 Comment

I got a question from Ryan: I have a bunch of excel cells and I need them all to have no spaces and no dashes so the first one would be: 185444001 and so on. How can I use ASAP Utilities to remove all spaces and dashes? You can do this very quick with the following utility: ASAP ...

ASAP Utilities how to with ASAP Utilities

How to: Delete blank lines in a cell with multiple lines of text?

Feb 10, 2009 Bastien 1 Comment

I'm sure you know you can enter a new line/line break by pressing Alt+Enter. But working with these newline characters can sometimes be a bit difficult, for example if you want to remove or replace them. If you have cells with multiple empty lines it can be difficult to quickly replace them all. Method 1: Do it ...

ASAP Utilities how to with ASAP Utilities

How to: Remove all blank rows at once

Feb 4, 2009 Bastien 0 Comment

If you have a lot of data it can be difficult to identify and remove the empty rows. There is no way in standard Excel to quickly remove all empty rows* so it can be a time-consuming job. You need to use a macro for that. Empty rows in your table can cause problems with sorting ...

ASAP Utilities how to with ASAP Utilities

When Excel doesn't recognize empty cells correctly

Nov 13, 2008 Bastien 0 Comment

Enno asked me the following: "... When I value-copy ranges where the If-function was used with a return value of "" (empty string), the cells are not blank, hence ISBLANK() = FALSE. Could you add a feature to remove empty strings? ..." Well, you can do this already with ASAP Utilities, although you cannot find it in the description of ...

ASAP Utilities General How to how to with ASAP Utilities

Unhide all columns and rows

Oct 21, 2008 Bastien 0 Comment

A short macro that will unhide all columns and rows in order to make all data visible again. Might be useful in some cases. Sub UnhideAllColumnsRows() ' unhide all columns and rows on the current worksheet Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False End Sub If you want more control on which rows and/or columns to unhide, you can also use this tool ...

ASAP Utilities how to with ASAP Utilities

Count the occurrences of a character in a cell

Jul 2, 2008 Bastien 5 Comment

Just got a question that I found interesting to mention here: I am looking for following feature in ASAP Utilities: Count number of user specified characters in a cell. e.g. I have a cell that has values "a,b,c,d,e". I would like to have a utility that tells me either a. there are 4 "," characters b. simply tell me ...

ASAP Utilities how to with ASAP Utilities

Video: How to fix trailing minus signs in Excel with ASAP Utilities

Nov 9, 2007 Bastien 5 Comment

The following video will show you how to how to fix trailing minus signs in Excel by using ASAP Utilities. This can be useful if you have imported data from a system that reports negative numbers with a trailing minus. If you've ever worked with imported numbers you might have faced the problem that negative numbers have ...

ASAP Utilities how to with ASAP Utilities

Video: How to delete all empty rows in Excel with ASAP Utilities

Nov 5, 2007 Bastien 0 Comment

Example file with empty rows that need to be removed

The following video will show you how to remove all empty rows in Excel by using ASAP Utilities. This can be useful if you have a large amount of data imported or copied together that you now want to clean up. An approach to remove all empty rows can be to sort the worksheet. However if you ...

ASAP Utilities how to with ASAP Utilities

Video: How to delete the first 3 characters in a column in Excel with ASAP Utilities

Oct 22, 2007 Bastien 0 Comment

How to delete the first 3 characters in a column - before

The following video will show you how to strip the first 3 characters from a column in Excel by using ASAP Utilities. The example in the video shows a list of article numbers. Now in this case we want to remove the first 3 characters. This can be done with an Excel formula such as =RIGHT(A2,LEN(A2)-3) in a ...

ASAP Utilities how to with ASAP Utilities

Video: How to fill empty cells with the value above in Excel with ASAP Utilities

Oct 22, 2007 Bastien 0 Comment

Copy values to empty cells below filled cells in selection - before

The following video will show you how to fill empty cells with the value above in Excel by using ASAP Utilities. The example in the video shows a table with several car-brands. Now in this case we want the brand-names copied down so that the empty cells are filled with the brand names. After this we can ...

ASAP Utilities how to with ASAP Utilities

Video: How to sort the sheet-tabs in Excel with ASAP Utilities

Oct 18, 2007 Bastien 1 Comment

The following video will show you how to sort the sheet-tabs in alphabetical order in Excel by using ASAP Utilities. The example in the video show a workbook with several sheets with city-names: In the Excel menu choose ASAP Utilities » Sheets » Sort all sheets in alphabetical order Then result will then look like: Note: This old video is no ...

ASAP Utilities how to with ASAP Utilities

Video: How delete leading, trailing and excessive spaces in Excel with ASAP Utilities

Oct 11, 2007 Bastien 0 Comment

Example of leading, trailing and excessive spaces

The following video will demonstrate you how to delete leading, trailing and excessive spaces from cells (trim) in Excel by using ASAP Utilities. The example in the video shows an address list with too many spaces: Several street- and city names contain a space at the beginning and/or at the end. Furthermore in a few names there are ...

ASAP Utilities how to with ASAP Utilities

Video: How to extract hyperlinks in Excel with ASAP Utilities

Oct 10, 2007 Bastien 1 Comment

The following video will demonstrate you extract hyperlinks from cells in Excel by using ASAP Utilities. The example in the video show a list of websites that I copied from my links-page. There is no easy way in Excel itself to get the hyperlinks from the cells. That's why I built such a functionality into ASAP Utilities. First ...

ASAP Utilities how to with ASAP Utilities

Video: How to sort by color in Excel with ASAP Utilities

Oct 9, 2007 Bastien 2 Comment

The following video will demonstrate you how to sort by color in Excel by using ASAP Utilities. It is not possible to sort by color in Excel. However we find that many people use colors to "tag" certain cells that need to be processed later. The example in the video show a list of keywords such as my ...

ASAP Utilities how to with ASAP Utilities

Video: How to remove leading zeros in Excel with ASAP Utilities

Oct 8, 2007 Bastien 0 Comment

The following video will demonstrate you how to remove (or trim) leading zeros from numbers within a range in Excel by using ASAP Utilities. This utility also works for numbers that are not recognized by Excel or are proceeded with an apostrophe (such as '2). You can usually recognize these numbers because they are left-aligned: The following video ...

ASAP Utilities how to with ASAP Utilities

Video: How to select/find unique values in a selection in Excel with ASAP Utilities

Oct 3, 2007 Bastien 0 Comment

The following video will demonstrate you how to select the unique values within a range in Excel by using ASAP Utilities. The video demonstrates how to select the color names that appear only once in the list: Note: This old video is no longer available and will in 2021 be replaced with a new version in a modern ...