Excel

Some handy formulas for Excel.

()CLEAN - can be used if additional quotation marks are appearing when using CONCATENATE

Show blank rather than 0 when sourcing a blank cell. Use: =A2 & "" e.g. =[SOURCE CELL] & ""

Combine Cells in Excel Without Concatenate

Combine Two Cells

In the cell where you'd like to see the combined values from two other cells:

Type an = sign, to start the formula

Click on the first cell that you want to combine

Type an &

Click on the second cell that you want to combine.

In the screenshot below, the product name and amount are being combined, and the formula is:

=B2&E2

Add a Space Between Combined Text

Your formula to combine the product name and price cells worked as advertised, but the results would look better with a space between the product name and price. To create a space, you can include a text string in the formula.

Select the cell with the formula

Click after the first cell reference

Type the & operator

Type " " (double quote, space, double quote)

Type the & operator

Press Enter to complete the revision

The revised formula is:

=B2&" "&E2

Excel shortcut tips.

To Edit a Macro in Excel

Controlling sort order

You can choose to sort a file so that lower case items are shown before uppercase items - note that this does not mean all lowercase items are shown first but shown at the top of the list alphabetically e.g. adam, Adam, ben, Ben, Ben etc.

• 1. Select the cells containing the information to be sorted.

  2. Choose Sort from the Data menu. Excel displays the Sort dialog box. (See Figure 1.)

• 1. Figure 1. The Sort dialog box.

  2. Using the controls on the dialog box, indicate the way in which the cells should be sorted.

  3. Click on the Options button. Excel displays the Sort Options dialog box. (See Figure 2.)

• 1. Figure 2. The Sort Options dialog box.

  2. Use the Case Sensitive check box to indicate how Excel should perform the sort. If the check box is selected, the sort is case sensitive; if clear, then case is ignored.

  3. Click on OK to close the Sort Options dialog box.

  4. Click on the OK button to perform your sort.

Change the case of text

Use the PROPER function =proper(TEXT) to convert the uppercase names to proper case, capitalising only the first letter in each name e.g. ben smith would become Ben Smith.

=lower(TEXT) converts all letters to lowercase.

=upper(TEXT) converts all letters to uppercase.

To extract a URL address from a hyperlink in Excel

Run the following Macro - this will insert the URL in the field next to the hyperlink:

Sub ExtractHL() Dim HL As Hyperlink For Each HL In ActiveSheet.Hyperlinks HL.Range.Offset(0, 1).Value = HL.Address Next End Sub

VLOOKUP Example: Vlookup with a Drop Down List