Excel tips

Assorted tips and advice for Microsoft Excel.

  2 minute read  

Here some practical tips for Microsoft Excel.

Strikethrough text

To draw a line (or strike through) text in a cell:

  1. Edit the cell
  2. Select the text
  3. Press Ctrl+5 (Windows)

Here, 5 represents the key in the number row of your keyboard, not the center key in a number pad.

For best results, format the cell to wrap overflow text.

String formula functions

Here are a few macros to help manipulate text strings

Find character in text

Use SEARCH to find a character within a string.

Example:

Given a cell (I2) containing F:\Stuff\accessibility\a_element.htm:

=SEARCH("\", I2)

Returns 14

Trim from string

Use RIGHT to remove characters from the beginning of a string and LEFT to remove characters from the end.

Example:

Given a cell (D2) containing F:\Stuff\accessibility\a_element.htm:

=RIGHT(D2,LEN(D2)-23)

Returns a_element.htm

Return variable substring

Combine these functions to return text based on the location of a character within a larger string.

Example:

Given a cell (I2) containing accessibility\accessibility\a_element.htm:

=LEFT( I2, SEARCH("\", I2 )-1)

Returns the first folder name (accessibility).

Date formula functions

Here are a few ways to use date functions in formulas.

Years from UTC dates

When working with dates expressed as string values, such as UTC data values, use string functions to reduce the value to one that can be converted to a date.

Example:

Given a cell (E2) containing Wed, 14 Jun 2017 07:00:00 GMT:

=YEAR(DATEVALUE(LEFT(E2,10)))

Returns 2017.

Quarters from UTC dates

Similar techniques can be used in more advanced cases.

Suppose you have a UTC date value and you want to turn that into a value appropriate for planning (17Q2):

  1. Determine the numeric month of the date:

    =YEAR(DATEVALUE(LEFT(E2,10)))  // E2 contains UTC date string

  2. Calculate the quarter for the numeric month:

    =IF(H2<4, 1, IF(H2<7,2,IF(H2<9,3,4)))  // H2 contains numeric month

  3. Assemble YYQQ string result:

    =CONCATENATE(RIGHT(G2,2),"Q",I2)

    Here:

    • Cell G2 contains the two digit year.
    • Cell I2 contains the quarter calculated earlier.