Examples of how ASAP Utilities will save you time and makes YOU rock in ExcelThese examples give you an idea how to solve certain common issues with the use of ASAP Utilities.
Here we show you some of the most asked questions we got either by email or found in the newsgroups or online forums that can be (or have been) answered by using ASAP Utilities.
Get the best out of ASAP Utilities with these examplesAlmost every week, we write a new tip/tutorial on our blog and via our Facebook page to show you how to benefit from the tools in ASAP Utilities.
Our last 20 tips/examples:
More detailed articlesMore examples are available on our blog
A few more simplified examples:Most of the below questions/answers are also described in chapter 7 of the User Guide which can be downloaded in PDF format.
Delete only the even rows in your spreadsheetIs there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones.
Automatically alternate row colorsHow can I set up alternating row colors in Excel? I don't want to have to change the row colors every time I insert, delete, or move a row.
Create a hyperlink to each sheet
Copy a long list of file namesI have a long list of file names (240 from a search) that I need to put into Excel. Copy won't do it. How now?
Extract numberHow can I get the numeric value from the cell: "$4.57 dividend"?
Removing spaces in cells with data in itWe have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no spaces.
In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date.
There are over 500 entries in this roster, is there a better way to remove the spaces other than manually?
Extracting Text onlyI'm trying to extract text only from a column of cells with varying text and number strings. Sometimes the cell starts with numbers, other times text. There is no constant for the number at the beginning of the cells.
Strip leading spaces from cellI need a formula to remove/delete leading spaces from a cell. Column b contains data imported from another application. Unfortunately some of these cells have padded leading blanks added. I need to remove these, but 3000+ cells individually will take hours. ASAP Utilities " Text " Delete leading and trailing spaces.
How to make duplicate entries blankI want to make a column that lists "city name" to only show each name once, and then leave each repeat of the name blank. Is there a formula for this?
Printing a Column in Multiple Segments on One PageI have a list of names in a spreadsheet column. The data takes up, say, ╝ of the page horizontally. Let's say I have 200 names, and want to print them in columns of 50. In other words, they should all be able to fit on one page - 1-50 in column 1, etc. I can cut and paste, but that's rather labor intensive. I can print 4-up, but that reduces the font size so that it's practically invisible.
Is there anything else I can do?
Trailing negatives imported from ASCII fileHow do I change a column of trailing negatives for example 123- to -123?
Deleting empty rowsIs there an easy (quick) way of deleting empty rows in a large spreadsheet?
Delete rows if Col C has a 0 value in itWhat formula please will delete rows if Col C has a 0 value in the cell?
Inserting rowsI have a spreadsheet that is approx. 200 rows long and I need to insert a new row after each existing row.
White Space in a CellI'm collating a lot of spreadsheets that many other people have filled in onto one big spreadsheet template.
My problem is that there is a free text cell and, because people don't know about the 'alt & enter' option, they've hit the space bar many many times make it look like a new point starts on a new line.
In Word, you have the option of showing all the keyboard strokes so you can tell what people have done - but I'm finding I have to go into each free text cell and hit delete, then the cell magically shifts up so that there is no white space.
Any ideas? My document is currently 200 pages long and I'm nowhere near done!
How do I conditionally delete rows based on cell contents?
Coloring every other row in a range
Removing Carriage Returns in ExcelTo remove only trailing carriage returns:
How to shade every third row?
How can I translate a comment to text in a cell?You can use our User Defined Function (UDF) =ASAPGetComment() to get the comment out of a cell.
How to convert Excel imported numbers from text to numbers?We have a software package from which we can export data to MS Excel. However, when exported it arrives in Excel as text. Even though they are numbers, they act like text. The only way to convert them text to numbers is to edit the cell (press F2) and hit enter. I've tried every other method of copying and pasting (even pasting special), but nothing seems to work.
Sorting Excel worksheets by worksheet title
Beginning zeros won't hold in field when saving as .CSVWhen saving an Excel spreadsheet as a .CSV (comma delimited) file, beginning zeros disappear. The only way I know to keep the zeros is to format the field as 'text.' However, .CSV files wipe out all formatting. Is there a way to KEEP the zero in the number '08' instead of '8' when saving as .CSV?
Print multiple sheets in one print jobI want to print several worksheets (e.g. sheet1, sheet 4, and sheet5) in one print job. This is a frequent task for users of one spreadsheet and would be much more convenient for them. I could probably write a macro to print each sheet individually, but it would be nice to print all at one time and have the printer staple them together.
Reverse the sign in a range of cells?I have a spreadsheet that is generated from an accounting program. It generates the debits as positive numbers and the credits as negative numbers. Is there a way to highlight a group of cells and reverse the sign on that group or range? There a few ways to do this. You can either multiply all values with -1:
Ending spaces need to be removedI have exported an SQL database table to a text file and need to do some work on it in Excel. Each field is comma-delimited. However, the table must have used fixed field lengths because the data now has blank spaces at the end of every field. I want to remove the extra spaces.
Auto-naming of Sheets?I have a list of numbers all 8 digits in length (roughly 200 numbers). I need a separate worksheet for each number. Is there a way of taking each number, inserting a worksheet and naming it according to the number? Select the cells with the numbers and then:
Printing question on a long listI have a fairly long excel sheet it's about 800-something rows but there aren't too many columns. How can I make it wrap up and print what would normally be 2 pages on to one page? The printer settings aren't working right and I'm getting it to be terribly small when I try and change those to fit. Do any of you know how i could do this? I'm pretty sure this is a really simple task and I just have never had to use excel for anything this long before or cared about manipulating it so that it could be done again.
Mass Remove HyperlinksI want to remove 1200 Hyperlinks, is it possible to do it mass?
Automate naming of tabsIn Excel 2002 XP I have a workbook with 13 spreadsheets. I need to automate the naming of the tabs on each spreadsheet so that each spreadsheet assumes the name of the contents of cell P1 for that respective spreadsheet. For instance, if the contents of cell P1 are 1999, the tab needs to be 1999. If the contents of cell P1 are changed to 2000, I need the tab to be 2000.
Leading zero conversion problemI have exported data from Outlook 2002 Contacts into an Excel 97 file. In the export process, all the leading zeros in the zip codes have become "'". For example, a Contacts zip code of 01075 has become '1075 in Excel. I tried using Excel "Find and Replace" to replace "'" with "0", but Excel can't find the "'" in the spreadsheet, although it shows in the cells.
You can either use the number format "00000" to have the values displayed with leading zeros, or use ASAP Utilities to add the zeros:
Protecting multiple worksheets easilyIs there a way to protect worksheets without having to do each one individually?
Sorting Worksheet TabsI really need to sort multiple worksheet tabs within a workbook. I would like to sort them numerically.
Newspaper ColumnsI have a long list of data consisting of three narrow columns. When I print the sheet, I'd like to repeat the set of three columns a few times per page (exactly like a telephone book) to prevent wasting paper.
Changing range of cells to absolute references?I am trying to figure out a way to change a table of formulas with multiple cell references to absolute cell references. Does anyone know how to do this without going into each cell and editing each one manually?
Mainframe download to Excel has minus sign at the end of the numberThe report has positive and negative numbers in columns. The positive amounts do not have a sign and are recognized in Excel as number format. The negative amounts have a "-" sign at the end of the number. Excel recognizes these as text format. Is there an easy way to reformat all the amounts with a negative sign to be recognized as number format in Excel?
Unhide hidden SheetsIs there a way to unhide multiple sheets at one time without code?
Making Cells AbsoluteI have a spreadsheet that has 75 columns with data that is from two other sheets. Stupid me when I did the formula I did not make it absolute, so now when I sort it the formulas change. Is there anyway I can make the formulas absolute without going into every cell and changing?
Save selection as jpegI have just bought a mobile phone which allows me to view jpeg images. I would like to carry around some information that I have on Excel sheets. Can I convert a sheet or a selection on a sheet into a jpeg image?
Password protecting multiple sheetsIs there a way to set a password for multiple sheets without having to set each one separately? I have over 60 sheets in one of my workbooks. I don't want to protect the whole workbook with a password because the file is used by other users but rather lock cells in each of the sheets with a password thereby allowing users to only edit certain cells. Because I have many packages to do, I don't want to be setting these passwords for over 600 sheets.
Reversing a cellI need a function that will reverse the data in a cell. For example if the value in a cell A1 is "xyzzy", then REV(A1) would be yzzyx. If the value in cell Z19 is "32767" then REV(Z19) would be 76723.
Edit Data - add *1000 to multiple cells, possible?I have a set of data and I want to multiply each cell by 1000 so want to add *1000 in about 30-40 cells together. Is there any way to do it quickly rather than going into each cell manually and entering *1000? ASAP Utilities " Formula " Apply formula to selected cells
How do you count the number of sheets in a workbook
The above examples are only a short list of the things that can be done with ASAP Utilities!
Follow ASAP Utilities on Facebook
List of all utilities
Printer friendly page
^ Back to Top