Switching chunks of cells

When I discussed the updated formulas for extracting two ISBNs in a single column to two other columns, I mentioned that if they weren’t in order and you ended up with the ISBN-13 in the ISBN-10 column, it was easy enough to switch them.

It was then pointed out that “easy enough” for me isn’t “easy enough” for everyone else. After all, I’ll bet there’s tons of people who think it’s “easy enough” run a storytime, cook a meal that involves more than one pot, or decide whether the fluids in your car need topping off, none of which I would attempt without careful preparation and/or some sort of instructions.

So today I’m going to talk about how to switch columns/rows/sets of cells in a spreadsheet, and some of the potential dangers in doing so. Continue reading Switching chunks of cells

Text function: finding sub-text

Sometimes you only care about a certain part of a string of text. For example, when reading a letter from a potential employer the first thing you care about is whether it says to schedule an interview, or to submit further information, or that they have selected someone else.

Obviously you’re not going to put a letter like that into Excel just to search for key phrases, mostly because it’s not worth the effort to do so for just one thing. However, there are lots of times when something is already in a spreadsheet, or can very easily be converted to a spreadsheet, when using this function can save you a whole lot of time.

For example, imagine a list of names in the “last, first” format. If you wanted to switch them you could use find() to get the location of the comma. Everything before the comma is the last name. Everything after it (except the leading space) is the first name. Continue reading Text function: finding sub-text

Project: Finding files

Note: I planning to follow up last week’s post about the len() function by discussing another text function: find(). Things happened, however, including a friend in the hospital, so I’ll put that off until next week.

Instead I’m going to share a project that I recently did which relied heavily on the find() function, and somewhat on the len() function. Sorry about the order — think of it as an illustration-in-advance, and I’ll explain the details of how the function actually works next week. Continue reading Project: Finding files

Text functions: finding length

Last week I said that this week I was going to talk about how to parse variable-format numbers into numbers. Basically, the problem with variable format numbers is that there are important things you don’t know. This is actually true of a lot of text manipulation once you get outside of the tidy examples I’ve been using up until now.

The simplest type of variable-format numbers is the variable-length one, where you don’t know how long it is, but all you need to do is get rid of a known number of characters at the beginning or the end. This can be addressed by using the function len(). Continue reading Text functions: finding length