While working in excel for IP addresses, got a task to generate new IP address based on an existing one in order to configure a device, after doing some Googling find few articles on this topic, there are two good approaches to it,   1. Using excel formula like REPLACE, MIN, LEN, MID, CONCATENATE, calculate new IP range, for e.g. Suppose your IP address is 10.76.192.0 and you like to generate new IP address […]

Suppose you have date time text in one column (format as YYYY-MM-DD) which you want to use in your concatenate function without changing custom format, because if you don’t do it then in concatenate function it will come in some numerical form for e.g. 40513 instead of 2010-12-01. It happens because excel ignores formatting.   Use below to fix it, Say column name is A1 where date value resides and you want to sue it […]

To quickly remove unwanted blank rows from long list in excel, do following, Select column having blank rows Press the F5 key on your keyboard (or select Edit, Goto). Click the Special… button. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected. Now right click and choose delete or choose Edit, Delete, select the Entire Row option and click OK.   Useful references: http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/ […]

Today I was struggling to insert a checkbox in Excel 2007, found a good tip as below, This can be done via Developer ribbon bar. If you do not see Developer as one of the tab options along the top (in the ribbon bar), do the following: 1. Click the Office logo in the top left corner 2. Down below besides Exit Excel you will find ‘Excel Options’, click it 3. In the ‘Popular’ window, […]