Parse Cell Value in Excel Using Regular Expressions

Using Excel formulas to parse a substring from a cell value is usually difficult especially if the cell value or pattern is not easily defined. I have found that it requires various combinations of SEARCH, FIND, LEFT, RIGHT, MID, etc. The resulting formula is both unreadable and it does not successfully handle all cell values thus resulting in manual clean up. It would be nice if the SEARCH or FIND functions were able to use regular expressions by default.

The following VBA function allows you to parse a cell value based on a regular expression. To use this function, you must enable Microsoft VBScript Regular Expressions 5.5.

  1. On the Developer ribbon, click Visual Basic
  2. Once the Visual Basic window opens, go to Tools -> References…
  3. From the References dialog box, check/enable Microsoft Regular Expressions 5.5
  4. Click the OK button

Now that the regular expression reference is activated, insert a new module into the workbook if one doesn’t already exist. The code will not work if added directly to a worksheet object.

Add the below code to the module. You can then use it as part of formulas in the workbook, e.g. =RegExParse(A1,”ID[0-9]+”). This would parse the value in cell A1 returning the substring matching the pattern ID[0-9]+, e.g. ID0, ID123, ID183274917234.

htaccess Redirects After Changing WordPress Permalink Structure

For my WordPress site, I had been using the “Day and name” permalink structure which includes the year, month, and day followed by the post name, e.g. /2013/07/06/sample-post/. When I re-installed WordPress due to a data loss, I changed to the “Post name” permalink structure, e.g. /sample-post/. Since the site had been previously indexed by search engines under the old permalink structure, the search results returned the old “Day and name” URLs which were now generating 404 response codes (Not Found).

In order to return the more appropriate response code of 301 (moved permanently) and redirect users to the new URL, you can modify the htaccess file to include redirects. This can be done by either creating a redirect for each specific page/post that moved or you can use a regular expression to execute the redirect from the older permalink structure to the new one.

As an example, I would add the following RedirectMatch line near the top of the htaccess file:

If you’re using WordPress, then your htaccess file might have the following instructions:

Blocking Referrer Links Through htaccess

In recent months, I’ve noticed a lot of incoming links from undesirable sites. I suppose any traffic is good traffic, but I would prefer to not have my content found through these spam sites. While I can’t prevent other sites from including a link to my site or copying my content, I can prevent them from directly linking to my images (hotlinking) or having users directed to my site from these other sites. With a little regular expression magic and my htaccess file, I can force an error to be returned or redirect the links somewhere else.

The first line instructs Apache to enable the runtime rewriting engine which allows it to process the subsequent rewrite conditions and rules.

This first section will block any requests where the originating site or referral site matches the established rewrite conditions. In this example, the conditions block requests from any site in the .cc, .eu and .ru top-level domains. The second condition line blocks requests from specific domains. The RewriteRule forces a 403 forbidden header to be returned to the originator.

This next section handles image hotlinking by redirecting any requests for .jpg, .jpeg, .gif or .png files where the originating request is not from my site. The RewriteRule forces a 403 forbidden header to be returned to the originator.

With everything together, the htaccess file should include the following in addition to any site specific htaccess code: