Tech Patrol

Given a list of phone numbers, and the need to turn these into international format,  you may need to combine some formulas.

Example:

Original phone number in Cell A2:     2223334444
Desired format:    +1 (222) 333-4444

Excel formula:
=CONCATENATE(“+1 (“,LEFT(A2,3),”) “,MID(A2,4,3),”-”,MID(A2,7,4)))

If you are looking to clear all formatting on an Excel spreadsheet, then Excel 2007 has just the feature.

Click the ‘Home’ tab in the ribbon, click on the ‘Clear’ icon in the ‘Editing’ section, and select ‘Clear Formats’

Nice and simple, and very useful.

When trying to delete a sheet programmatically in Excel, an alert is displayed asking you to confirm if you want to delete the sheet.

To remove this, you can use the following code:

Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

The alerts will be switched off for this action and then turned on again.

A common task when analysing data is to highlight values to show the data.

To highlight a row based on the value of one cell you need to use the INDIRECT function in Excel.

This allows you to use conditional formatting to highlight a whole ROW based on the value of one of the cells in the row.  (Useful if you have used a count function to highlight duplicates)

Example:

Lets say you have numbers in column B, and you want to highlight all the rows where the value in the column equals “2”.

You can use the formula below in the conditional formatting rule to create the entry:

=INDIRECT("B" & ROW())=2

You can amend how you want to format the values as normal.

After a recent laptop hard drive failure I have been checking out some home backup solutions to backup that valuable data.

After a long search it comes down to 2 contenders; Acronis True Image Home and Nero BackItUp.

Both allow you to backup files and your system, however where Acronis is streets ahead is the recovery software.  True Image Home allows you to create a disk/partition image of your operating system, back this up to a portable hard drive so that in the event of any drive failure, a restore can be achieved with speed.  Acronis gives you the ability to create rescue media to either a CD or even a bootable USB stick, which given the abundance of netbooks without CD drives is streets ahead.

You can use the Excel formula ISNA to remove excel producing ‘#N/A’ results in cells.

This is especially useful when using VLOOKUP functions which seach for values in one column and return values in other columns.

How to use it:

Using ISNA is conjunction with IF, you can create a formula that will replace #N/A when it appears.

=IF(ISNA(“<yourformula>”),”",”<yourformula>”)

for example:

=IF(ISNA(VLOOKUP(A1,B1:B26,1,FALSE)),”",VLOOKUP(A1,B1:B26,1,FALSE))

Ctrl + Pg Up / Pg Dn allows you to change worksheet in Excel

http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-HP005203781.aspx

I have been struggling for a while to amend my a WordPress theme to relocate the sidebar to the left hand pane.

There are a few articles posted on the support site which give a few idea’s but I though I would summarise what is necessary.  http://wordpress.org/support/topic/217419?replies=7

Try set the .narrowcolumn float to “right” and remove the #sidebar “margin-left”

I found that once I had done that I still had to adjust the column widths to then shift the text over to align it with the background.  These are the two extracts that I have used:

.narrowcolumn {
float: right;
padding: 0 45px 20px 45px;
margin: 0px 0 0;
width: 440px;
}

#sidebar
{
padding: 20px 0 10px 25px;
‘    margin-left: 45px;
width: 160px;
}

Keyboard shortcuts to get yourself around Excel much quicker.

http://office.microsoft.com/en-gb/excel/HP052037811033.aspx

Today’s Quick Tip:

Command line switches are available for launching Windows Explorer (explorer.exe)

Information available from Microsoft here:

http://support.microsoft.com/kb/130510