Text and code formulas in a calculated column
An address in one line, a code with leading zeros, an ID that must not leave the building, an amount as “1,234.50” instead of 1234.5 — this usually means a macro or a trip to Excel. Tabulens has functions for it in the calculated column: regular expressions, splitting by a delimiter, SHA-256 hash, Base64, URL and HTML encoding, number and date formatting.
Free to use. “Transform” → “Calculated column…”. Nothing is uploaded. Windows version — 5.4 MB, no installation: details. Pro — $29 once: price.
The functions
| Function | What it does |
|---|---|
REGEXEXTRACT(x, 'pattern', [group]) | part of the text by a regular expression; the group is in parentheses, without parentheses the whole match is returned; no match gives an empty value |
REGEXREPLACE(x, 'pattern', with) | replaces every match |
SPLITPART(x, ';', n) | the n-th part by a delimiter, counted from one; −1 is the last; spaces at the edges are trimmed |
SHA256(x, [salt]) | SHA-256 hash of “salt + value”; an empty value stays empty |
BASE64(x), UNBASE64(x) | Base64 encoding and decoding |
URLENCODE(x), URLDECODE(x) | space ↔ %20 and the rest, for URLs |
HTMLENCODE(x), HTMLDECODE(x) | escapes and restores < > & |
NUMFMT(x, decimals, [group], [point]) | a number to text with the decimals and separators you choose |
DATEFMT(date, 'mask') | a date to text by a mask: YYYY, YY, MM, DD, hh, mm, ss |
It is the same language as the usual formulas and the filter line, so functions nest and combine with IF, TRIM and the rest.
Examples
| Task | Formula |
|---|---|
| House number from “12 Elm St, No. 15” | REGEXEXTRACT(ADDRESS, 'No\. ?(\d+)') |
| Strip leading zeros from a code | REGEXREPLACE(CODE, '^0+', '') |
| The last word — a surname from “FULL_NAME” | SPLITPART(FULL_NAME, ' ', -1) |
| The same code instead of an ID for everyone who knows the salt | SHA256(ID, 'your-salt') |
| An amount as “1,234.50” | NUMFMT(AMOUNT, 2, ',', '.') |
| A date as “05.03.2024” or “20240305” | DATEFMT(BIRTH, 'DD.MM.YYYY'), DATEFMT(BIRTH, 'YYYYMMDD') |
Regular expressions ignore case, and \w in them means a letter of any alphabet, not only Latin: Cyrillic, accented letters and CJK all match.
A hash instead of personal data
SHA256(ID, 'salt') gives the same code for the same ID, so two exports can be joined and compared by these codes without anyone seeing the numbers. Without a salt the hash of a short number is easy to brute-force, so use one and keep it like a password.
If you only need a sequence number rather than a code, use “Anonymize”: it replaces values with numbers in order.
In a scenario
Any such formula can be a scenario step. In the “＋” catalog, formula steps have a “Text and codes” group with ready-made starters: part of the text by a pattern, a date or number to text by a mask, pad with zeros on the left, remove leading zeros, SHA-256 hash with salt. You only replace the column and the pattern.
The salt is written in the formula, so it is in the scenario file as well. Pass the scenario only to people who may know the salt.
FAQ
What if the pattern finds nothing?
REGEXEXTRACT returns an empty value and REGEXREPLACE leaves the text as it is. You can filter the empty ones like any other column.
How do I take parts from the end?
A negative number in SPLITPART: −1 is the last part, −2 the one before it.
Can the result be written into the file?
A calculated column is not written to the file. Its menu has “Write values into the column”: the values go in as an edit and are saved like any other edit.
Can a hash be reversed?
No: SHA-256 is one-way. A value can only be recovered by guessing, which is why short numbers need a salt.
Free to use. Your file is not uploaded to a server. Windows version — 5.4 MB, no installation: details. Pro — $29 once: price.