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

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}

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)

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))