HOW TO prefix zeroes before a variable length number in Excel

Excel usually eats up any zeroes prefixed to a number that you type. So what if you explicitly needed them?

For a fixed length number, you have to use the option Format Cells after right-clicking the cell or range that you want to impose a number format upon. In the dialog box that opens go to to the Number tab, pick the Custom option & specify the number format. If you wanted the number 8217 or any 4 digit number to have 5 zeroes prefixed before it, you would have to specify the number format as 000000000 so that it shows up as 000008217. The trick here is to know what the meta-characters in the format mean.

Browsing through the generally good documentation by F1-ing, I did not find a format to handle variable length numbers for the scenario just described. I got a solution from a MS Knowledgebase article, which is to use the format "00000"#. The double quotes in the number format helps us prefix the desired number of zeroes before a variable length number.

Related:
Excel Tips & Tricks

Read More

Excel Tips & Tricks

My Wife & I, share a liking for Excel (& good food). Once in a while, we exchange Excel tips & tricks that we may have picked up. I recognized this common interest a couple of years ago, when she pointed out that the count of rows & columns have thankfully increased since Excel 2003 and marvelled at the improvements in Charts in Excel 2007 with the same excitement that is usually reserved for a special dish. I did not loose a chance then to tell her how to get a Ribbon-less pre-Excel 2007 UI.

Just so that I do not forget the tips & tricks we discuss, I plan to document them here.

1) Find & replace a special character - This trick is adapted from a response on the MrExcel Forum

Let's say you have received an Excel file with a pesky non printing character at several places in a sheet and now you need to replace it with something else. How to go about it?

Get the numeric code of the pesky character using the CODE function. For instance, the formula =CODE("!") will display the numeric code for ! or 33

Next, invoke the Find box using the shortcut Ctrl + F. Click in the Find box, hold Alt key and type 0 followed by numeric code of the mysterious character you have encountered(for example, for "!", you would type 033 instead of 33), on the number keypad and then the release the Alt key. As soon as you release the Alt key the character whose numeric code you typed will appear in the Find box.

Now go to the Replace tab, place the character to substitute in the "Replace with" textbox, and hit Replace All.

Excel Help suggests in the topic "Remove spaces and non printing characters from text" that you can also use a combination of the TRIM, CLEAN, and SUBSTITUTE functions to remove non printing characters

2) Matching row, values of adjacent cells - I find this question frequently being asked on technical Forums. To get a result that is based on values of adjacent cells in a row, use the combination of INDEX & MATCH as explained in the topic "Look up values in a list of data" in Excel Help.

3) (Added: 17-Sep-09) Increment numeric values in a range of cells by a standard value - Jonathan Van Houtte's tip shows you how to add a value of 10 to large number of existing cells containing (let's say) salary amounts at one shot.

1. Type 10 in any empty cell.
2. Copy that cell.
3. Select the range of cells containing Salary amounts
4. Right-Click, and choose Paste Special.
5. On the Paste Special dialog box, click the Add radio button (in the Operation frame)
6. Click "OK"
7. Clear the cell with the 10.

To be continued...

Also see:
HOW TO create an Excel 2007 Macro
HOW TO conditionally format entire row/s based on a cell's text value in Excel 2007

Read More

Exporting to Excel with bcp

I was recently looking for a quick way to export SQL Server table data (over 1,00,000 records) to a Excel file.

I found that the bcp command line tool can export SQL Server table data to a .xls file but when I export it to a Excel 2007 (.xlsx) file, I couldn't open the file. It throws a warning - "Excel cannot open the file because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

The fix to this issue reported in ASP.NET Forums which points to a MS KB article, is complicated.

I wondered, if bcp can export to a .xls file why can't it smoothly export to a .xlsx file?

Curiously, I was able to able to export over 7 lakh records to a .xls file (which is supposed to have a row limit of ~65K rows in 22seconds) with bcp and the .xls file opens fine in Excel 2007 displaying all the 7 lakh records.

Vinod helped me understand how the process actually works -

Fundamentally, you need to understand that the BCP command doesn’t actually write as a real XLS file. Try to open it in Notepad and it would be a mere csv format. It is just that Excel seamlessly opens it without any problem. Now with .XLSX extension Excel 2007 expects the same to be in the XML format (which it isn’t actually) and hence the error.

BTW, the format of XLS or XLSX doesn’t determine the number of rows. It is the version of Office (2003/2007) you are running.

Read More

HOW TO get date N working days ahead in Excel

I use Excel occasionally and I'm always amazed at how there is always a function that you can adapt to suit a requirement.

To get date 3 working days ahead in Excel there is a helpful WORKDAY function.

It however returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Working days exclude weekends and any dates identified as holidays. WORKDAY can be used to exclude weekends or holidays

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

So the formula to get a DATE 3 working days ahead in Excel takes a little more work as we have to convert the serial number representing the resulting date -

=DATE(YEAR(WORKDAY(A1,3)),
MONTH(WORKDAY(A1,3)),
DAY(WORKDAY(A1,3)))

It is assumed here that you have a valid date in cell A1

Many of the Excel functions including WORKDAY are available in Google Docs Spreadsheet as well.

Read More

HOW TO conditionally format entire row/s based on a cell's text value in Excel 2007

I found that Conditionally Formatting an entire row/s rather than a single cell and that too  based on a text value in a cell rather than a numeric value in Excel 2007 is not straight forward. After a bit of monkeying around I learnt you can also actually have multiple Formulas while specifying a Rule for Conditional Formatting.

I jotted down the steps to do this with screen shots and posted them on my Code Gallery.

If you know of a simpler or alternative way to Conditionally Format an entire row in Excel 2007, please let me know.

Also see:

HOW TO create an Excel 2007 Macro

HOW TO create a Ribbon-less Excel 2003 UI in Excel 2007

Read More