Code and Data Manipulation

Sublime Text, Excel, etc

Find and Replace with Regex

Regex to find date and time.

Option 1: ([\d]{1,2})/([\d]{1,2})/([\d]{4}) in format mm/dd/yyyy

Option 2: [0-9]{4}-[0-9]{2}-[0-9]{2}T in format yyyy-mm-dd

Option 3: ([0-1]?[0-9]|2[0-3]):[0-5][0-9] in format HH:mm

In Sublime Text Find and Replace (CMD+OPT+F) replace with $3-$1-$2

Sublime Regex Date

This replaces date mm/dd/yyyy with yyyy-mm-dd

The below will verify email address format.

Simple:

[\w\-\.]+@([\w-]+\.)+[\w\-]{2,4}

Complex

^(?:(?!.*?[.]{2})[a-zA-Z0-9](?:[a-zA-Z0-9.+!%-]{1,64}|)|\"[a-zA-Z0-9.+!% -]{1,64}\")@[a-zA-Z0-9][a-zA-Z0-9.-]+(.[a-z]{2,}|.[0-9]{1,})$

GUID, alphanumeric 8-4-4-4-12

[0-9A-Fa-f]{8}[-][0-9A-Fa-f]{4}[-][0-9A-Fa-f]{4}[-][0-9A-Fa-f]{4}[-][0-9A-Fa-f]{12}

Find Data in Cell

This document is in development

SEARCH finds "text value" in cell A1.

IF result of SEARCH is that the data is found, result returns TRUE. Else, FALSE.

=IF(SEARCH("text value",A1,1),TRUE,FALSE)

Count Unique Values in Spreadsheet

This document is in development

UNIQUE finds the unique values in a spreadsheet row or column.

COUNTA counts the results of UNIQUE, result returns a numeric value.

=COUNTA(UNIQUE(A1:B100))

Excel Count Unique Values