Counting text occurrences in Numbers

I wanted to summarize the data in a spreadsheet of information about use of Capital Bikeshare bikes. The data contain start and end station and several other variables. I just wanted to count the number of times a bike was checked out from a station in Fairfax County. The data are located on the Capital Bikeshare site. Currently the data are stored in zip files by month.

The data were downloaded, unzipped, and the csv file was located into Mac Numbers. Stations in Fairfax County have station numbers from 32200 to 32236. I sorted by station number, then cut an pasted this range into a new sheet. At the bottom of the data I added cells that contained just the station name. Then I used this formula in the adjacent column to count the occurrence of each station name in the data above:

=COUNTIF($E$2:$E$624,A627)

Where $E$2:$E$624 is the absolute cell reference for the range of station names. A627 is the station name, a relative reference so that when the formula is copied and pasted to the next row, the next station name will be compared and counted.

Merging images with GIMP

Open GIMP and open the first image. Open another image either with Preview or GIMP, select and copy the image, then paste into GIMP. In the Layers menu the pasted image will appear as a Pasted Layer. Select that layer and click on Create a new layer in the lower left of the window (looks like a blank page with a plus sign in the upper left). The pasted layer should now be selected. Move the layer to the new position. It won’t appear beyond the limit of the old image unless it’s created as a new layer. Then under the Image menu select “Fit canvas to layers” and the second image should now appear in a new, larger canvas. Repeat for additional images.

Google Sheets duplicate rows

If a Google Sheets spreadsheet has rows with duplicates, there’s a way to highlight where the duplicates are located, and then manually delete them: From LifeWire:

Open the spreadsheet you want to analyze in Google Sheets.

Highlight the column you want to search through.

Click Format > Conditional Formatting. The Conditional Formatting menu opens on the right.

Confirm the cell range is what you selected in Step 2.

In the Format cells if… drop-down, select Custom formula is. A new field appears below it.

Enter the following formula in the new field, adjusting the letters for the column range you selected:

=countif(A:A,A1)>1
In the Formatting style section, choose a fill color for the duplicate cells. In this example, we’ve chosen red. Then click Done.

Enlarging the canvas using Preview

I wanted to create an image containing two other images of about the same size. Preview doesn’t have a tool for enlarging the canvas. I found this workaround on apple.stackexchange:

  1. Select all (cmd+A) and cut (cmd+X) the image from its canvas (convert to png if asked). 
  2. Resize the image as desired from the “Tools” menu (unlock the proportions if desired)
  3. Re-paste original image from the clipboard (cmd+V) and move it where you want it on the resized canvas
  4. Use cmd+- if necessary to zoom out (you’ll see checkerboard where the canvas is and white beyond)
  5. Paste in other images, etc, as you like

Using Regular Expressions in Search and Replace

Using Google docs, regular expressions can be used to conduct search and replace operations. I had a list of email addresses that were preceded by the address in quotes. I wanted to remove the quotes and everything between them: “Name5@something.com”

This expression found everything up to the @ sign: [“][a-zA-z0-9.]+ (I assume A-z should be A-Z)

This expression found everything: [“][a-zA-z0-9.]+[@][a-zA-z0-9.]+[“]

The replacement was null. Regex cannot be used for replacement expressions in google docs. However, BBedit does allow regex for replacement.

In this example I wanted to capitalize the first letter of each sentence in a text file. I use \n to find the new line before the next sentence. I use and then used parens to save what was found, and [a-z] to find all lower case letters that come after a new line:

\n([a-z])

The lower case letter after the new line found with the above expression was saved in the expression \1. To make that lower case letter upper case I used \U before \1

\n\U\1

(this info was found in a BBEdit tutorial)

From the Google help file for regular expressions:

ExpressionDescriptionExampleMatchesDoes not match
.A period represents any character in the given position.d.do, dog, dg, adsfog, jog
*An asterisk after a character represents a search for that preceding character repeated 0 or more times.do*gdog, dg, dooogdOg, doug
+A plus after a character represents a search for that character displayed 1 or more times.do+gdog, dooogdg, dOg, doug
?The previous expression is optional.do?gdg, dogdOg, doug
^A caret must be placed at the beginning of a regular expression. It signifies the string starts with the character(s) or sequence placed after the caret. Note: This regular expression only works with Google Sheets.^[dh]ogdog, hogA dog, his hog
$A dollar sign must be placed at the end of a regular expression and signifies that the string ends with the character(s) or sequence placed before the dollar sign.Note: This regular expression only works with Google Sheets.[dh]og$dog, hog, hot dogdogs, hogs, doggy
{A, B}The previous expression is repeated between A and B times, where A and B are numbers.d(o{1,2})gdog, doogdg, dooog, dOg
[x], [xa], [xa5]A character set indicates that just one of the given character(s) should occur in the current position. Usually, any characters are valid within brackets, including characters mentioned previously in expressions: [xa,$5Gg.]d[ou]gdog, dugdg, dOg, dooog
[a-z]A character set range signifies a search for a character within the given range of characters. Common ranges include a-z, A-Z, and 0-9. Ranges can be combined into a single range: [a-zA-Z0-9]. Ranges can also be combined with character sets (mentioned previously): [a-zA-Z,&*].d[o-u]gdog, dug, dpg, drgdg, dOg, dag
[^a-fDEF]A character set beginning with a ^ signifies a search for a character that is not within the given set.d[^aeu]gdog, dOg, dig, d$gdg, dag, deg, dug
\sAny white-space character.d\sgd g, d[TAB]gdg, dog,

Deleting media from iMovie

When attempting to remove some files from iMovie I came across a media clip that I wanted to delete. When I attempted to delete the file, I was told I didn’t have access and I should change permissions on the file. Unfortunately I couldn’t find the file using the filename since it was embedded in the iMovie package. The way to delete the file is to navigate to the folder Movies, then Get Info on the iMovie library file and select Show Package Contents. Navigate to the project folder and select Original Media. Get Info on the file that can’t be deleted and uncheck the Locked checkbox. Then return to iMovie and now the project and file can be deleted.