## Smart Columns

Published On February 26, 2021

# Introduction

Smart columns are a great way for you to quickly calculate sums on your entire dataset, and have them just as editable as any other column!

# Caveats

The editor does not function like a traditional grid based editor (Google sheets, Excel etc). In order to allow you to edit and insert rows/columns in multi gigabyte files with zero latency, the file is kept firmly on disk and streamed as you view it, because of this - there are limitations imposed on the smart functions which you would not normally expect:

• The formula can only lookup values in the current row. Each row is processed individually and thus other rows are not referenceable.
• Smart columns evaluate from left to right. If you want to reference another smart column, the referenced column should be to the left. This is to ensure there are no circular dependencies.
• Deleting a source column will result in a #REF lookup error.
• Saving the file will commit the smart column(s) the calculated value in each cell will be written to the csv.
• You can then reopen the file to delete source columns

# Operators

The formula syntax supports these basic operations, the syntax is parsed in a left to right approach (not BODMAS). Please use brackets to achieve desired functionality.

Operator
Description
+
-
Subtract
/
Divide
*
Multiply
%
Modulus
&
Concatenate (Strings)
(
Start group
)
End group
A-Z
Reference column
12.3
Decimal
FUNC(a,b)
Functions (see below)

# List of functions

The formula box provides access to many functions that can be useful

##### Decimal Functions
Function
Description
CONCAT(a,b)
Appends b onto a
LEFT(a,x)
First x characters of a
RIGHT(a,b)
Last x characters of a
SUBSTITUTE(a, b, c)
Replaces all instances of b with c located in a
Adds b to a (equivalent of +)
SUBTRACT(a,b)
Subtracts b from a (equivalent of -)
MULTIPLY(a,b)
Times a and b (equivalent of *)
DIVIDE(a,b)
Divides a by b (equivalent of /)
MOD(a,b)
Remainder of a by b (equivalent of %)
MAX(a,b)
Greater of a or b
MIN(a,b)
Lesser of a or b
ROUND(a,x)
Rounds a to x decimal places
POW(a, b)
Takes a to the power of b
IF(a, b, c)
Checks a is true, returns b otherwise returns c
COL(x)
Returns the Xth column
AND(a,b)
Checks if a and b are both true
OR(a,b)
Checks if a or b are either true
XOR(a,b)
Checks if one of a or b is true
EQUALS(a,b)
Checks if a and b are the same value
NOTEQUALS(a,b)
Checks if a and b are different values
NOT(a)
Checks if a is true, returns false, vice versa
GT(a,b)
Checks if a is greater than b
LT(a,b)
Checks if a is less than b
##### String Functions
Function
Description
Concat(string1, string2)
Appends string2 onto string1 (equivalent of &)
Left(string, No characters)
First X characters of the string
Right(string, No characters)
Last X characters of the string
Substitute(string, old, new)
Replaces all instances of old with new located in string
Trim(string)
Removes whitespace either side of the string
Lowercase(string)
Converts the string to lowercase
Uppercase(string)
Converts the string to uppercase
Reverse(string)
Reverses the string
Startswith(string, pattern)
Checks if the string starts with the pattern returns 1 or 0
Endswith(string, pattern)
Checks if the string ends with the pattern returns 1 or 0
Contains(string, patter)
Checks if the string contains the pattern returns 1 or 0
Len(string)
Returns the number of characters in a string
Regex(string,pattern,group)
Return the selected regex group from a pattern match
IsMatch(string, pattern)
Returns true if the regex pattern was found in the string
##### Conditional Functions
Function
Description
IF(condition, a, b)
Checks the condition and returns A if true or B otherwise
##### Lookup Functions
Function
Description
Col(index)
Returns the value in the specified column for each row (equivalent of A, B, C etc)

# Examples

##### Prefix a £ symbol to A column values
`\'£\'&A `
##### Find the Sum of columns A B and C, and prefix a £ symbol
`\"£\"&A+B+C `
##### Find the average of columns A, B and C
`(A+B+C)/3 `
##### Calculate column A with an additional 20% tax (rounded)
`ROUND(A*1.2, 2) `
##### Extract the first name from a first/last pair using regex
`REGEX(B,'^(.+)\\s(.+)',1)`
##### Print even or odd if there are an even or odd number of characters in a column
`IF(LEN(B) % 2, 'Even', 'Odd')`
##### Swapping Columns A and B

You can add two new columns C and D,

C will reference B

`B`

and D will reference A:

`A`

Now save the file to commit both smart columns

Once the file reloads you can delete Colums A and B to leave only the reversed columns:

##### Checking a string contains Fish
`IF(CONTAINS(A,'Fish'), 'Contains Fish', 'No Allergy')`
Tutorials