Specific functions: Middle

As I mentioned last week, 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 any other formatted data, as well as for proofreading, generating statistics, and writing code. While left and right are fairly intuitive, the middle function can seem somewhat less so.

As I also mentioned before, the middle function takes characters from inside a string of text, but that doesn’t mean it starts in the middle and works its way outward or something. It takes a specified number of characters, beginning with the character you tell it to start with. If you used middle(“This is a string of text”,9,7) you’d get “a string”. That’s 4 characters for “This”, 1 space, and 2 for “is”, and then another space, making the “a” in “a string” the 9th character. Then there’s the 1 character for “a”, 1 space, and 5 letters in “string”, for a total of 7 characters.

If you tell it to start with the first character of a string it will act just like left(). If you tell it to start with the last character, though, it won’t replace right(), since it starts with the character you specify, and goes right from there. You’d have to tell it to start at the last character minus however many characters you want returned, if you want the middle function to emulate right().

On the flip side, you can use right and left to emulate middle. If you wanted, say, the 4th and 5th characters of a string you could take the left five characters, and then the right 2 characters of that result.

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 varies slightly by program, but is generally consistent.

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 middle function treats named groups as items, meaning they only pull information from the nearest cell of that group. Unless specified otherwise, start# can be any positive integer (1, 2, 3…), and length# can be any non-negative integer (0, 1, 2…). As with right and left, if you specify more characters than are available, most spreadsheets will just give you what’s there.

In Excel 97, in Excel 2007, in Google Spreadsheet, and in Microsoft Works Spreadsheet:

  • Syntax: MID(text1,start#,length#)
  • Category: Text

In OpenOffice Calc:

  • Syntax: =MID(text1;start#;length#)
  • Category: Text

In Microsoft Works Spreadsheet:

  • Syntax: =MID(text1,start#,length#)
  • Category: Text