At times, applying various operations to your data can help extract meaningful insights. In this guide, we'll walk you through the steps of adding a formula column to your labsheets/sheets. This special column is a useful tool for building calculated columns in your scientific databases. Please find all the supported formulas in the Formula Syntax Reference File attached at the end of this article.
Step 1: Open your labsheet, and edit the column that you want as a 'formula' column. Alternatively, add a new column if the column doesn't exist. Make sure it is a 'Free text' or 'Number' column. Formulas are not supported for other types of columns.
Step 2: Enter the required formula in the formula field.
Remember, when adding the formula, start with an equal (=) sign, then use the formula command in parentheses ( ), and reference the column name in square brackets [ ]. Make sure the column names inside [ ] resemble the actual column name in your Labsheet.
Note: Make sure the input columns for your formulas belong to the same Labsheet as your output.
You can also directly type your scientific notation inside Labsheet or sheets (Eg, 1.2e+3)
Verify the formula syntax and then hit 'Save'
Here are the most common formulas that customers write on their Labsheets in Scispot:
Function | Formula | Explanation |
Mean | =AVERAGE([Column_Name]) | Calculates the average of all the values in the specified column. |
Median | =MEDIAN([Column_Name]) | Finds the middle value in a set of numbers that has been arranged in ascending or descending order. |
Variance | =VAR([Column_Name]) | Measures the squared deviations from the mean of a set of numbers. |
Sum | =SUM([Column_Name]) | Adds up all the values in the specified column. |
Minimum | =MIN([Column_Name]) | Finds the smallest value in the specified column. |
Maximum | =MAX([Column_Name]) | Finds the largest value in the specified column. |
Range | =MAX([Column_Name]) - MIN([Column_Name]) | Calculates the difference between the largest and smallest values in the specified column. |
Interquartile Range | =QUARTILE([Column_Name], 0.75) - QUARTILE([Column_Name], 0.25) | Measures the variability of a set of data by calculating the difference between the 75th and 25th percentiles. |
Correlation | =CORREL([Column_Name1], [Column_Name2]) | Measures the strength and direction of the linear relationship between two variables. |
Covariance | =COVARIANCE([Column_Name1], [Column_Name2]) | Measures the degree to which two variables vary together. |
P-value for T-Test | =T.TEST([Group1_Column_Name], [Group2_Column_Name], tails=2) | Calculates the probability of obtaining a t-statistic as extreme or more extreme than the observed result, assuming that the null hypothesis is true. |
Rank | =RANK([Column_Name], [Column_Name], 0) | Assigns a rank to each value in the specified column, where the lowest value is ranked 1 and the highest value is ranked n. |
Q-value | =ADJUST([P-value_Column_Name], method="BH") | Adjusts the p-values for multiple comparisons using the Benjamini-Hochberg procedure. |
ANOVA | =F.ONEWAY([Group1_Column_Name], [Group2_Column_Name], [Group3_Column_Name]) | Performs an analysis of variance to compare the means of two or more groups. |
Log | =LOG10([Column_Name]) | Calculates the base-10 logarithm of each value in the specified column. |
Log2 fold change | =LOG2([Condition1_Column_Name]) - LOG2([Condition2_Column_Name]) | Measures the change in expression levels between two conditions by calculating the difference in log2 values. |
Average | =AVERAGE([Column_Name]) | Calculates the average of all the values in the specified column. |
Standard Deviation | =STDEV([Column_Name]) | Measures the variability of a set of numbers by calculating the square root of the average squared deviations from the mean. |
Z-score | =(ABS([Column_Name]) - AVERAGE([Column_Name])) / STDEV([Column_Name]) | Standardizes a value by calculating its number of standard deviations away from the mean. |
Pearson Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2]) | Measures the strength and direction of the linear relationship between two variables. |
Spearman Rank Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2], type="Spearman") | Measures the strength and direction of association between two ranked variables. |
Kendall Tau Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2], type="Kendall") | Measures the strength and direction of association between two variables. |
Division | =[Column_Name1] / [Column_Name2] | Calculates the quotient of two numbers. |
Multiplication | =[Column_Name1] * [Column_Name2] | Calculates the product of two numbers. |
Addition | =[Column_Name1] + [Column_Name2] | Calculates the sum of two numbers. |
Subtraction | =[Column_Name1] - [Column_Name2] | Calculates the difference of two numbers. |
Greater Than | =[Column_Name1] > [Column_Name2] | Compares two numbers and returns TRUE if the first number is |
Less Than | =[Column_Name1] < [Column_Name2] | Compares two numbers and returns True if the first number is less than the second number |
Quantile (25th percentile) | =quantile([Column_Name], 0.25) | Value below which 25% of the data falls |
Quantile (50th percentile) | =quantile([Column_Name], 0.5) | Median value |
Quantile (75th percentile) | =quantile([Column_Name], 0.75) | Value above which 25% of the data falls |
To convert a number string to scientific notation on your Labsheet setup, you can follow these steps:
Step-by-Step Guide:
Add a Column:
In your Labsheet, add a new column. This can be a free text or a number-type column.
Apply the Scientific Notation Formula:
Use the following formula in the newly added column. Replace
[column name]with the actual name of the column containing the numbers you wish to convert.
=(LEFT([column name], 1) & "." & MID([column name], 2, 2) & "e" & (LEN([column name]) - 1))
Example:
Replace
[column name]with your actual column identifier. For example, if your column name is 'Cell Density', the formula would be:
=(LEFT([Cell Density], 1) & "." & MID([Cell Density], 2, 2) & "e" & (LE([Cell Density]) - 1))
This will transform numbers like "12345" into "1.23e4".
Formula Locking
Formula Locking is a new feature that ensures formula-generated values remain consistent across both the UI and API responses. The new Formula Lock button allows you to finalize and synchronize column values before they become searchable or filterable.
Unlocked formulas (default):
Formula columns start in an unlocked state and are highlighted in amber.Locking a formula column:
When editing a column, click the padlock icon next to the formula to lock it. Once locked, the column cannot be unlocked.Locked formulas:
Locked formula columns are highlighted in grey and become fully searchable and filterable.
Formula Syntax Reference File
Please find all the supported formulas in the PDF attached below.
Information
Function ID | Description | Syntax |
ISBINARY | Returns TRUE if provided value is a valid binary number. | ISBINARY(Value) |
ISBLANK | Returns TRUE if the reference to a cell is blank. | ISBLANK(Value) |
ISERR | Returns TRUE if the value is error value except #N/A!. | ISERR(Value) |
ISERROR | Returns TRUE if the value is general error value. | ISERROR(Value) |
ISEVEN | Returns TRUE if the value is an even integer, or FALSE if the value is odd. | ISEVEN(Value) |
ISFORMULA | Checks whether referenced cell is a formula. | ISFORMULA(Value) |
ISLOGICAL | Tests for a logical value (TRUE or FALSE). | ISLOGICAL (Value) |
ISNA | Returns TRUE if the value is #N/A! error. | ISNA(Value) |
ISNONTEXT | Tests if the cell contents are text or numbers, and returns FALSE if the contents are text. | ISNONTEXT(Value) |
ISNUMBER | Returns TRUE if the value refers to a number. | ISNUMBER(Value) |
ISODD | Returns TRUE if the value is odd, or FALSE if the number is even. | ISODD(Value) |
ISREF | Returns TRUE if provided value is #REF! error. | ISREF(Value) |
ISTEXT | Returns TRUE if the cell contents refer to text. | ISTEXT(Value) |
SHEET | Returns sheet number of a given value or a formula sheet number if no argument is provided. | SHEET([Value]) |
SHEETS | Returns number of sheet of a given reference or number of all sheets in workbook when no argument is provided. | SHEETS([Value]) |
NA | Returns #N/A! error value. | NA(Value) |
Logical
Function ID | Description | Syntax |
AND | Returns TRUE if all arguments are TRUE. | AND(Logicalvalue1, Logicalvalue2 ...Logicalvalue30) |
FALSE | Returns the logical value FALSE. | FALSE() |
IF | Specifies a logical test to be performed. | IF(Test, Then value, Otherwisevalue) |
IFNA | Returns the value if the cell does not contains the #N/A (value not available) error value, or the alternative value if it does. | IFNA(Value, Alternate_value) |
IFERROR | Returns the value if the cell does not contains an error value, or the alternative value if it does. | IFERROR(Value, Alternate_value) |
NOT | Complements (inverts) a logical value. | NOT(Logicalvalue) |
SWITCH | Evaluates a list of arguments, consisting of an expression followed by a value. | SWITCH(Expression1, Value1[, Expression2, Value2[ ..., Expression_n, Value_n]]) |
OR | Returns TRUE if at least one argument is TRUE. | OR(Logicalvalue1, Logicalvalue2, ..., Logicalvalue30) |
TRUE | The logical value is set to TRUE. | TRUE() |
XOR | Returns true if an odd number of arguments evaluates to TRUE. | XOR(Logicalvalue1, Logicalvalue2, ..., Logicalvalue30) |
Lookup and reference
Function ID | Description | Syntax |
CHOOSE | Uses an index to return a value from a list of up to 30 values. | CHOOSE(Index, Value1, ..., Value30) |
COLUMN | Returns column number of a given reference or formula reference if argument not provided. | COLUMNS([Reference]) |
COLUMNS | Returns the number of columns in the given reference. | COLUMNS(Array) |
FORMULATEXT | Returns a formula in a given cell as a string. | FORMULATEXT(Reference) |
HLOOKUP | Searches horizontally with reference to adjacent cells to the bottom. | HLOOKUP(Search_Criterion, Array, Index, Sort_Order) |
INDEX | Returns the contents of a cell specified by row and column number. The column number is optional and defaults to 1. | INDEX(Range, Row [, Column]) |
MATCH | Returns the relative position of an item in an array that matches a specified value. | MATCH(Searchcriterion, Lookuparray, Type) |
OFFSET | Returns the value of a cell offset by a certain number of rows and columns from a given reference point. | OFFSET(Reference, Rows, Columns, Height, Width) |
ROW | Returns row number of a given reference or formula reference if argument not provided. | ROW([Reference]) |
ROWS | Returns the number of rows in the given reference. | ROWS(Array) |
VLOOKUP | Searches vertically with reference to adjacent | VLOOKUP(Search_Criterion, Array, Index, Sort Order) |
Operator
Function ID | Description | Syntax |
HF.ADD | Adds two values. | HF.ADD(Number, Number) |
HF.CONCAT | Concatenates two strings | HF.CONCAT(String, String) |
HF.DIVIDE | Divides two values. | HF.DIVIDE(Number, Number) |
HF.EQ | Tests two values for equality. | HF.EQ(Value, Value) |
HF.LTE | Tests two values for less-equal relation. | HF.LEQ(Value, Value) |
HF.LT | Tests two values for less-than relation. | HF.LT(Value, Value) |
HF.GTE | Tests two values for greater-equal relation. | HF.GTE(Value, Value) |
HF.GT | Tests two values for greater-than relation. | HF.GT(Value, Value) |
HF.MINUS | Subtracts two values. | HF.MINUS(Number, Number) |
HF.MULTIPLY | Multiplies two values. | HF.MULTIPLY(Number, Number) |
HF.NE | Tests two values for inequality | HF.NE(Value, Value) |
HF.POW | Computes power of two values. | HF.POW(Number, Number) |
HF.UMINUS | Negates the value. | HF.UMINUS(Number) |
HF.UPLUS | Applies unary plus. | HF.UPLUS(Number) |
HF.UNARY_PERCENT | Applies percent operator. | HF.UNARY_PERCENT(Number) |
Statistical
Function ID | Description | Syntax |
AVEDEV | Returns the average deviation of the arguments. | AVEDEV(Number1, Number2, ...Number30) |
AVERAGE | Returns the average of the arguments. | AVERAGE(Number1, Number2, ...Number30) |
AVERAGEA | Returns the average of the arguments. | AVERAGEA(Value 1, Value2, ... Value30) |
AVERAGEIF | Returns the arithmetic mean of all cells in a range that satisfy a given condition. | AVERAGEIF(Range, Criterion [, Average_Range ]) |
CHIDISTRT | Returns probability of chi- square right-side distribution. | CHIDISTRT(X, Degrees) |
CHISQ.DIST.RT | Returns probability of chi- square right-side distribution. | CHISQ.DIST.RT(X, Degrees) |
CHISQ.INV | Returns inverse of chi-square distribution. | CHISQ.INV.RT(P, Degrees) |
CHISQ.INV.RT | Returns inverse of chi-square right-side distribution. | CHISQ.INV.RT(P, Degrees) |
CHISQ.TEST | Returns chisquared-test value for a dataset. | CHISQ.TEST(Array1, Array2) |
CHITEST | Returns chisquared-test value for a dataset. | CHITEST(Array1, Array2) |
CONFIDENCE | Returns upper confidence bound for normal distribution. | CONFIDENCE(Alpha, Stdev, Size) |
CONFIDENCE.NORM | Returns upper confidence bound for normal distribution. | CONFIDENCE.NORM( Alpha, Stdev, Size) |
CONFIDENCE.T | Returns upper confidence bound for T distribution. | CONFIDENCE.T(Alpha, Stdev, Size) |
CORREL | Returns the correlation coefficient between two data sets. | CORREL(Datal, Data2) |
COUNT | Counts how many numbers are in the list of arguments. | COUNT(Value1, Value2, ... Value30) |
COUNTA | Counts how many values are in the list of arguments | COUNTA(Value1, Value2, ... Value30) |
COUNTBLANK | Returns the number of empty cells. | COUNTBLANK(Range) |
COUNTIF | Returns the number of cells that meet with certain criteria within a cell range. | COUNTIF(Range, Criteria) |
COUNTIFS | Returns the count of rows or columns that meet criteria in multiple ranges. | COUNTIFS(Rangel, Criterion1 [, Range2, Criterion2 [,...]]) |
COVAR | Returns the covariance between two data sets, population normalized. | COVAR(Datal, Data2) |
COVARIANCE.P | Returns the covariance between two data sets, population normalized. | COVARIANCE.P(Data1, Data2) |
COVARIANCEP | Returns the covariance between two data sets, population normalized. | COVARIANCEP(Data1, Data2) |
COVARIANCE.S | Returns the covariance between two data sets, sample normalized. | COVARIANCE.S(Datal, Data2) |
COVARIANCES | Returns the covariance between two data sets, sample normalized. | COVARIANCES(Data1, Data2) |
DEVSQ | Returns sum of squared deviations. | DEVSQ(Number1, Number2, ...Number30) |
EXPON.DIST | Returns density of a exponential distribution. | EXPON.DIST(Number1, Number2, Boolean) |
EXPONDIST | Returns density of a exponential distribution. | EXPONDIST(Number1, Number2, Boolean) |
FDIST | Returns probability of F right-side distribution. | FDIST(X, Degree1, Degree2) |
FINV | Returns inverse of F right-side distribution. | FINV(P, Degree1, Degree2) |
F.DIST | Returns value of F distribution. | F.DIST(X, Degree1, Degree2, Mode) |
F.DIST.RT | Returns probability of F right- side distribution. | F.DIST.RT(X, Degree1, Degree2) |
FDISTRT | Returns probability of F right- side distribution. | FDISTRT(X, Degree1, Degree2) |
F.INV | Returns inverse of F distribution. | F.INV.RT(P, Degreel, Degree2) |
F.INV.RT | Returns inverse of F right-side distribution. | F.INV.RT(P, Degreel, Degree2) |
FINVRT | Returns inverse of F right-side distribution. | FINVRT(P, Degree1, Degree2) |
FISHER | Returns Fisher transformation value. | FISHER(Number) |
FISHERINV | Returns inverse Fischer transformation value. | FISHERINV (Number) |
F.TEST | Returns f-test value for a dataset. | Z.TEST(Array1, Array2) |
FTEST | Returns f-test value for a dataset. | ZTEST(Array1, Array2) |
GAMMA | Returns value of Gamma function. | GAMMA(Number) |
GAMMA.DIST | Returns density of Gamma distribution. | GAMMA.DIST(Number1, Number2, Number3, Boolean) |
GAMMADIST | Returns density of Gamma distribution. | GAMMADIST(Number1, Number2, Number3, Boolean) |
GAMMALN | Returns natural logarithm of Gamma function. | GAMMALN(Number) |
GAMMALN.PRECISE | Returns natural logarithm of Gamma function. | GAMMALN.PRECISE(Number) |
GAMMA.INV | Returns inverse Gamma distribution value. | GAMMA.INV(Number1, Number2, Number3) |
GAMMAINV | Returns inverse Gamma distribution value. | GAMMAINV(Number1, Number2, Number3) |
GAUSS | Returns the probability of gaussian variable fall more than this many times standard deviation from mean. | GAUSS(Number) |
GEOMEAN | Returns the geometric average. | GEOMEAN(Number1, Number2, ...Number30) |
HARMEAN | Returns the harmonic average. | HARMEAN(Number1, Number2, ...Number30) |
HYPGEOMDIST | Returns density of hypergeometric distribution. | HYPGEOMDIST(Number1, Number2, Number3, Number4, Boolean) |
HYPGEOM.DIST | Returns density of hypergeometric distribution. | HYPGEOM.DIST(Number1, Number2, Number3, Number4, Boolean) |
LARGE | Returns k-th largest value in a range. | LARGE(Range, K) |
LOGNORM.DIST | Returns density of lognormal distribution. | LOGNORM.DIST(X, Mean, Stddev, Mode) |
LOGNORMDIST | Returns density of lognormal distribution. | LOGNORMDIST(X, Mean, Stddev, Mode) |
LOGNORM.INV | Returns value of inverse lognormal distribution. | LOGNORM.INV(P, Mean, Stddev) |
LOGNORMINV | Returns value of inverse lognormal distribution. | LOGNORMINV(P, Mean, Stddev) |
LOGINV | Returns value of inverse lognormal distribution. | LOGINV(P, Mean, Stddev) |
MAX | Returns the maximum value in a list of arguments. | MAX(Number1, Number2, ...Number30) |
MAXA | Returns the maximum value in a list of arguments. | MAXA(Value1, Value2, ... Value30) |
MAXIFS | Returns the maximum value of the cells in a range that meet a set of criteria. | MAXIFS(Max_Range, Criterion_rangel, Criterion] [, Criterion_range2, Criterion2 [,...]]) |
MEDIAN | Returns the median of a set of numbers. | MEDIAN(Number1, Number2, ...Number30) |
MIN | Returns the minimum value in a list of arguments. | MIN(Number1, Number2, ...Number30) |
MINA | Returns the minimum value in a list of arguments. | MINA(Value1, Value2, ... Value30) |
MINIFS | Returns the minimum value of the cells in a range that meet a set of criteria. | MINIFS(Min_Range, Criterion_rangel, Criterion1 [, Criterion_range2, Criterion2 [....]]) |
NEGBINOM.DIST | Returns density of negative binomial distribution. | NEGBINOM.DIST(Number1, Number2, Number3, Mode) |
NEGBINOMDIST | Returns density of negative binomial distribution. | NEGBINOMDIST(Number1, Number2, Number3, Mode) |
NORM.DIST | Returns density of normal distribution.
| NORM.DIST(X, Mean, Stddev, Mode) |
NORMDIST | Returns density of normal distribution. | NORMDIST(X, Mean, Stddev, Mode) |
NORM.S.DIST | Returns density of normal distribution. | NORM.S.DIST(X, Mode) |
NORMDIST | Returns density of normal distribution. | NORMSDIST(X, Mode) |
NORM.INV | Returns value of inverse normal distribution. | NORM.INV(P, Mean, Stddev) |
NORMINV | Returns value of inverse normal distribution. | NORMINV(P, Mean, Stddev) |
NORM.S.INV | Returns value of inverse normal distribution. | NORM.S.INV(P) |
NORMSINV | Returns value of inverse normal distribution. | NORMSINV(P) |
PEARSON | Returns the correlation coefficient between two data sets. | PEARSON(Datal, Data2) |
PHI | Returns probability densitity of normal distribution. | PHI(X) |
POISSON | Returns density of Poisson distribution. | POISSON(X, Mean, Mode) |
POISSON.DIST | Returns density of Poisson distribution. | POISSON.DIST(X, Mean, Mode) |
POISSONDIST | Returns density of Poisson distribution. | POISSONDIST(X, Mean, Mode) |
RSQ | Returns the squared correlation coefficient between two data sets. | RSQ(Datal, Data2) |
SKEW | Returns skeweness of a sample. | SKEW(Number1, Number2, ...Number30) |
SKEW.P | Returns skeweness of a population. | SKEW.P(Number1, Number2, ...Number30) |
SKEWP | Returns skeweness of a population. | SKEWP(Number1, Number2, ... Number30) |
SLOPE | Returns the slope of a linear regression line. | SLOPE(Array1, Array2) |
SMALL | Returns k-th smallest value in a range. | SMALL(Range, K) |
STANDARDIZE | Returns normalized value wrt expected value and standard deviation. | STANDARDIZE(X, Mean, Stddev) |
STDEV | Returns standard deviation of a sample. | STDEV(Value1, Value2, ... Value30) |
STDEVA | Returns standard deviation of a sample. | STDEVA(Value1, Value2, ... Value30) |
STDEVP | Returns standard deviation of a population. | STDEVP(Value1, Value2, ... Value30) |
STDEV.P | Returns standard deviation of a population. | STDEV.P(Value1, Value2, ... Value30) |
STDEVPA | Returns standard deviation of a population. | STDEVPA(Value1, Value2, ... Value30) |
STDEV.S | Returns standard deviation of a sample. | STDEV.S(Value1, Value2, ... Value30) |
STDEVS | Returns standard deviation of a sample. | STDEVS(Value1, Value2, ... Value30) |
STEYX | Returns standard error for predicted of the predicted y value for each x value. | STEYX(Array1, Array2) |
TDIST | Returns density of Student-t distribution, both-sided or right-tailed. | TDIST(X, Degrees, Mode) |
T.DIST | Returns density of Student-t distribution. | T.DIST(X, Degrees, Mode) |
T.DIST.2T | Returns density of Student-t distribution, both-sided. | T.DIST.2T(X, Degrees) |
TDIST2T | Returns density of Student-t distribution, both-sided. | TDIST2T(X, Degrees) |
T.DIST.RT | Returns density of Student-t distribution, right-tailed. | T.DIST.RT(X, Degrees) |
TDISTRT | Returns density of Student-t distribution, right-tailed. | TDISTRT(X, Degrees) |
TINV | Returns inverse Student-t distribution, both-sided. | TINV(P, Degrees) |
T.INV | Returns inverse Student-t distribution. | T.INV(P, Degrees) |
T.INV.2T | Returns inverse Student-t distribution, both-sided. | T.INV.2T(P, Degrees) |
TINV2T | Returns inverse Student-t distribution, both-sided. | TINV2T(P, Degrees) |
TTEST | Returns t-test value for a dataset. | TTEST(Array1, Array2) |
T.TEST | Returns t-test value for a dataset. | T.TEST(Array1, Array2) |
VAR | Returns variance of a sample. | VAR(Value1, Value2, ... Value30) |
VARA | Returns variance of a sample. | VARA(Value1, Value2, ... Value30) |
VARP | Returns variance of a population. | VARP(Value1, Value2, ... Value30) |
VAR.P | Returns variance of a population. | VAR.P(Value1, Value2, ... Value30) |
VARPA | Returns variance of a population. | VARPA(Value 1, Value2, ... Value30) |
VAR.S | Returns variance of a sample. | VAR.S(Value1, Value2, ... Value30) |
VARS | Returns variance of a sample. | VARS(Value1, Value2, ... Value30) |
WEIBULL | Returns density of Weibull distribution. | WEIBULL(Number1, Number2, Number3, Boolean) |
WEIBULL.DIST | Returns density of Weibull distribution. | WEIBULL.DIST(Number1, Number2, Number3, Boolean) |
WEIBULLDIST | Returns density of Weibull distribution. | WEIBULLDIST(Number1, Number2, Number3, Boolean) |
Z.TEST | Returns z-test value for a dataset. | Z.TEST(Array, X[, Sigma]) |
ZTEST | Returns z-test value for a dataset. | ZTEST(Array, X[, Sigma]) |
Text
Function ID | Description | Syntax |
CHAR | Converts a number into a character according to the current code table. | CHAR(Number) |
CLEAN | Returns text that has been "cleaned" of line breaks and other non-printable characters. | CLEAN("Text") |
CODE | Returns a numeric code for the first character in a text string.
| CODE("Text") |
CONCATENATE | Combines several text strings into one string. | CONCATENATE("Text1",. "Text30") |
EXACT | Returns TRUE if both text strings are exactly the same. | EXACT(Text, Text) |
FIND | Returns the location of one text string inside another. | FIND("Text1", "Text2"[, Number]) |
LEFT | Extracts a given number of characters from the left side of a text string. | LEFT("Text", Number) |
LEN | Returns length of a given text. | LEN("Text") |
LOWER | Returns text converted to lowercase. | LOWER(Text) |
MID | Returns substring of a given length starting from Start_position. | MID(Text, Start_position, Length) |
PROPER | Capitalizes words given text string. | PROPER("Text") |
REPLACE | Replaces substring of a text of a given length that starts at given position. | REPLACE(Text, Start_position, Length, New_text) |
REPT | Repeats text a given number of times. | REPT("Text", Number) |
RIGHT | Extracts a given number of characters from the right side of a text string. | RIGHT("Text", Number) |
SEARCH | Returns the location of one text string inside another. (Allows the use of wildcards.) | SEARCH("Text1", "Text2"[, Number]) |
SPLIT | Divides the provided text using the space character as a separator and returns the substring at the zero-based position specified by the second, argument. SPLIT("Lorem ipsum", 0)-> "Lorem" |SPLIT("Lorem ipsum", 1) -> "ipsum" | SPLIT(Text, Index) |
SUBSTITUTE | Returns string where occurrences of Old_text are replaced by New_text. Replaces only specific occurrence if last parameter is provided. | SUBSTITUTE(Text, Old_text, New_text, [Occurrence]) |
T | Returns text if given value is text, empty string otherwise. | T(Value) |
TEXT | Converts a number into text according to a given format. | TEXT(Number, Format) |
TRIM | Strips extra spaces from text. | TRIM("Text") |
UNICHAR | Returns the character created by using provided code point. | UNICARH(Number) |
UNICODE | Returns the Unicode code point of a first character of a text. | UNICODE(Text) |
UPPER | Returns text converted to uppercase. | UPPER(Text) |