When is a spreadsheet not a spreadsheet?

I know that sounds like the opening to a “frayed knot” joke, but I couldn’t come up with a suitable punchline. However, it’s still a valid question that I think it’s time to address.

There are two types of data formats that aren’t exactly spreadsheets, but naturally and logically belong in the same category: Machine-readable formats, and human readable formats.

The first type, machine-readable formats, can be read by a human, but they can be somewhat difficult to interpret. For example, in comma delimited format each cell value is separated by a comma, and each row is separated by a new line. Which leads to files like this example:
Continue reading When is a spreadsheet not a spreadsheet?

Specific functions: Trim

The trim function is a deceptively simple one. All it does is remove excess spaces from a string. However, it makes my list of favorite text manipulation functions because it’s applicable in so many different situations. Like my other favorite text manipulation functions, I use it for proofreading manuscripts. It’s quite useful when manipulating text files, such as MARC records and video game FAQs. It’s also good when working with information from the web, like job postings and lists of available journals. I frequently use it to clean up my concatenates, and I understand that it can also be helpful when cleaning up results from SPSS. I find it invaluable when working with someone else’s code (either HTML or programming), because the indentation is often different than what I find easiest to read.
Continue reading Specific functions: Trim

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.
Continue reading Specific functions: Middle

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.
Continue reading Specific functions: Right and Left