Regular Expression, Operators, and Other Shortcuts for Excel and SEO

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

google

bing

facebook

 

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”
site:seomoz.org

filter specific results containing multiple queries for a specific domain

Pages indexed from a specific domain including multiple queries

“query string A” OR “query
string B” site:domain.tld

“search engine optimization” OR
“seo” site:seomoz.org

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

Connect with Amanda Thomas on Google+

0 Comments

  1. Pingback: Regular Expression, Operators, and Other Shortcuts for Excel and SEO | gangster.co.in

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>