Specific functions: Right and Left

The right, left, and middle functions aren’t very glamorous, but they are workhorses. I often use them together, but not always. They’re useful for date handling, and for interpreting MARC records or other formatted data, as well as for proofreading, generating statistics, and writing code. Today I’m going to specifically cover the “left” and “right” functions.

As I mentioned before, the “left” function returns a specified number of characters from the start of a string of text. If you used left(“This is a string of text”,7) you’d get “This is”. That’s 4 characters for “This”, 1 space, and 2 for “is”.

The “right” function returns a specified number of characters from the end of a string of text. If you used right(“http://swissarmyspreadsheets.wordpress.com”,4) you’d get “.com”.

General Instructions
As with any function, you start by selecting the cell you want the function to be in, and then enter it either by typing or by using the menus to retrieve it. The syntax, category, and quirks vary somewhat by program. If you ask for more characters than the string has — for example, left(“abcd”,5) — then most programs will just return the entire string.

No matter what spreadsheet program you’re using, if I say text1 I mean you can enter text inside quotation marks, or a reference to or name of a cell or range of cells. In most programs, the left and right functions treat named groups as items, meaning they only pull information from the nearest cell of that group. If I say # I mean any non-negative integer (0, 1, 2…). Most programs will also assume that if you don’t give it a number then you want just a single character.

In Excel 97, Excel 2007, and Google Spreadsheet:

(Yes, they all have the same syntax this time!)

  • Syntax:
    • Left: =LEFT(text1,#) or =LEFT(text1)
    • Right:=RIGHT(text1,#) or =RIGHT(text1)
  • Category: Text

In OpenOffice Calc:

  • Syntax:
    • Left: =LEFT(text1;#) or =LEFT(text1)
    • Right: =RIGHT(text1;#) or =RIGHT(text1)
  • Category: Text

In Microsoft Works Spreadsheet:

  • Syntax:
    • Left: =LEFT(text1,#)
    • Right: =RIGHT(text1,#)
  • Category: Text
  • Notes: You must enter a number.