I love shortcuts (so long as they still do the job right without causing harm). When it comes to Excel, I am a big fan of finding ways to analyze data quicker and more effectively. However, if you’re using Excel or Word to analyze your Internet marketing data, you might be interested in finding other shortcuts to help you save time and effort. In my opinion, Regular Expression is a great tool for this, just so long as you know how to use it properly. As with any tool, it’s only as good as the knowledge of, and care given by, the person wielding it. Here’s some information to help you utilize some of these tools for your Internet marketing campaigns.
Microsoft Excel
In Excel, you might find yourself in a position where you need to find certain words or strings but there may be some slight variance. Thankfully, you can use a question mark to act as a single character wildcard, or you can use an asterisk as a wildcard to detect whole strings (instead of being limited to a single character). I’ve already covered the asterisk in a previous post if you want to review some other examples of using an asterisk in Excel.
| Usage | Find & Replace | Operator | Example | Finds | Also Finds | Doesn’t Find |
| find and/or replace | Single character | ? | d?g | dog | dig | drag |
| find and/or replace | String (one or more characters) | * | d*g | dog | drag | rag |
I also love to use keyboard shortcuts in Excel. You can view the whole list of Excel shortcuts at Microsoft Office’s website and you can also find some other shortcuts and tricks to using Excel on the Best Rank blog as well. Here are just a few of my favorite keyboard shortcuts:
|
Command |
Description |
|
CTRL+PgUp |
Switches between worksheet tabs, from left to right. |
|
CTRL+PgDn |
Switches between worksheet tabs, from right to left. |
|
CTRL+B |
Applies or removes bold formatting. |
|
CTRL+C |
Copies the selected cells. |
|
CTRL+D |
Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below. |
|
CTRL+I |
Applies or removes italic formatting. |
|
CTRL+K |
Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks. |
|
CTRL+R |
Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right. |
|
CTRL+U |
Applies or removes underlining. |
|
CTRL+V |
Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents. |
|
CTRL+X |
Cuts the selected cells. |
|
CTRL+Y |
Repeats the last command or action, if possible. |
|
CTRL+Z |
Uses the Undo command to reverse the last command or to delete the last entry that you typed. |
|
CTRL+1 |
Displays the Format Cells dialog box. |
|
CTRL+2 |
Applies or removes bold formatting. |
|
CTRL+3 |
Applies or removes italic formatting. |
|
CTRL+4 |
Applies or removes underlining. |
|
CTRL+5 |
Applies or removes strikethrough. |
|
F2 |
Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off. |
|
F4 |
When a cell reference or range is selected in a formula, F4 cycles through the various combinations of absolute and relative references. |
|
F12 |
Displays the Save As dialog box. |
|
CTRL+ARROW KEY |
Moves to the edge of the current data region in a worksheet. |
|
CTRL+SHIFT+ARROW KEY |
Extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell. |
|
CTRL+SHIFT+END |
Extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar. |
|
CTRL+HOME |
Moves to the beginning of a worksheet. |
|
CTRL+SHIFT+HOME |
Extends the selection of cells to the beginning of the worksheet. |
|
ALT+ENTER |
Starts a new line (break) in the same cell. |
|
SHIFT+TAB |
Moves to the previous cell in a worksheet or the previous option in a dialog box. |
|
CTRL+TAB |
Switches to the next tab in dialog box. |
|
CTRL+SHIFT+TAB |
Switches to the previous tab in a dialog box. |
Microsoft Word
You can even use a few other find and replace codes in Word to help you find and/or replace specific characters (paragraph marks and tabs are particularly tricky since you can’t hit enter or tab within the Find and Replace window without it selecting the next button or running the find function). Here’s a quick overview of these shortcuts:
|
Usage |
Character |
Code |
|
find and/or replace paragraph breaks |
Paragraph mark |
^p |
|
find and/or replace tabs |
Tab |
^t |
|
find and/or replace em dashes |
Em dash (—) |
^+ |
|
find and/or replace en dashes |
En dash (–) |
^= |
|
find and/or replace numbers |
Any number (0-9) |
^# |
|
find and/or replace letters |
Any letter (a-z, A-Z) |
^$ |
|
find and/or replace characters |
Any character (letters and non-letters) |
^? |
Google Analytics
If you’re like me, though, you sometimes need to have your data filtered before you export it and manage it within Excel or Word. That is where I find custom reports and using regular expression in Google Analytics to be an incredibly helpful use of my time. There are so many ways that you can use regular expression with Google Analytics and Annie Cushing has a great article about using RegEx for Google Analytics that you really should read. Here are just a few of my favorites:
Usage: filter data for specific
sources (dimension: source)
|
Results |
Operator |
Example |
Finds |
Also Finds |
Doesn’t Find |
|
Contains specific criteria |
| |
google|bing|yahoo |
|
bing |
|
Usage: filter geo-specific
keywords (dimension: keyword)
|
Results |
Operator |
Example |
Finds |
Also Finds |
Doesn’t Find |
|
Contains a string (wildcard) |
* |
san diego * |
san diego internet marketing |
san diego seo |
california internet marketing |
Usage: filter data for specific
URL paths (dimension: page)
|
Results |
Operator |
Example |
Finds |
Also Finds |
Doesn’t Find |
|
Starts with |
^ |
^/tag/ |
/tag/ |
/tag/page.html |
/folder/tag/ |
Usage: filter data for specific
URL paths or file extensions excluding parameters (dimension: page)
|
Results |
Operator |
Example |
Finds |
Also Finds |
Doesn’t Find |
|
Ends with |
$ |
aspx$ |
/page.aspx |
/page2.aspx |
/page.aspx?color=blue |
Search Queries
There are other uses for Regular Expression within the context of SEO as well. Both Himanshu Sharma and Rob Millard provide some helpful insight on utilizing RegEx for SEO purposes, particularly when it comes to URL rewriting using .htaccess files. There are also some other RegEx-like tricks that you can use for filtering search query results. These tricks can be used for site audits (particularly to spot canonicalization issues), keyword research, link building prospecting, and competitor analysis. You can view an extended list of uses of Google’s site:operator written by Dr. Pete or, if you’re really adventurous and want to utilize these similar tactics with other search engines, SEOmoz has provided an excellent article breaking down the advanced search operators that you can use for your Internet marketing efforts as well. Here are just a few examples that I use the most:
|
Usage |
Results |
Syntax |
Example |
|
filter queries to specific domain |
Pages indexed from a specific domain |
site:domain.tld |
site:http://www.seomoz.org |
|
filter queries to see how many pages are indexed for www URLs for a given domain |
Pages indexed from a specific domain only those within the www sub-domain |
site:http://www.domain.tld |
site:http://www.seomoz.org |
|
filter queries to see how many pages are indexed for www URLs for a given domain |
Pages indexed from a specific domain excluding those in the www sub-domain |
site:http://domain.tld |
site:http://seomoz.org |
|
filter specific results containing a query for a specific domain |
Pages indexed from a specific domain including a specific query |
query site:domain.tld |
seo site:seomoz.org |
|
filter specific results containing a query for a specific top level domain |
Pages indexed for a specific query for specific top-level domains |
query site:.tld |
seo site:.edu |
|
filter specific results containing an exact match query for a specific domain |
Pages indexed from a specific domain including an exact match for a specific query |
“query string” site:domain.tld |
“search engine optimization” |
|
filter specific results containing multiple queries for a specific domain |
Pages indexed from a specific domain including multiple queries |
“query string A” OR “query |
“search engine optimization” OR |
|
filter specific results containing wildcards |
Pages indexed that include any results matching your “wildcard-ed” query |
“query * string” |
“6 to 1 * to the other” |
|
filter specific results containing a query for a specific domain excluding a specified keyword |
Pages indexed from a specific domain excluding specific query |
-query site:domain.tld |
-blog site:seomoz.org |
