Remove zeros from the beginning, middle, or end of data points using RegEx.
Removing extra zeros (or any character or set of characters) is a powerful data cleaning task used to extract usable information from difficult to process source data. When the data followed specific patterns, as things like automatic logs commonly do, we can use Regular Expressions to create flexible statements which process the data. In Excel, you can use RegexExtract.
Using the Find/Replace Object gives us the option to activate RegEx by clicking the asterisk (*) symbol so that it turns blue. Now, the Find and Replace fields can accept plain text or RegEx statements.
This recipe will load with explanations of each Find/Replace Object that was used. We take advantage of patterns at the start of an expression (using the begins with ^ character), patterns at the end of an expression (using the ends with $ character), and using groups (by enclosing statements in parentheses, and referencing them with $1 or $3).