Calculate field sharepoint with functions“calculated-column”-text-and-data

One of the more useful categories of functions available to use in Calculated Columns is that of the “Text and Data” set of formulas.

Through their use you can combine fields, compare data existing in other columns, apply formatting rules to entered data, and a variety of other actions to get the most out of the data that gets entered into lists and libraries.

The Microsoft Office site lists out the various functions we’ll be discussing – which I’d encourage each of you to bookmark as an invaluable tool for future reference (

The available functions in this category are as follows:

ASC – Converts “full-width” (double-wide) characters into their corresponding “half-wide” (single-byte) equivalents. Useful in language conversions where the typical set of “ASCII” characters are not enough to encompass the (possible) thousands of characters used in the language (also see “Double-Byte Character Sets in Windows“).  (Example: full-width text “ファズ・ギター” converts to half-width “ファズ・ギター” equivalent – notice the size and spacing of the characters?)

CHAR – Used in converting ANSI numerical values to their corresponding character values.

CODE – Similar to the “Code” function, but used in converting a character into its corresponding ANSI numerical value.

CLEAN – Strips out all non-printable characters from text.  Used to format text that may have originated from a separate program or data source that in its output includes low-level code (such as “” blocks) that can’t be printed.

CONCATENATE – Used to combine (join) sets of strings into a single string.  This action “pre-pends” or “appends” one or more (up to 30) strings together into a single string of text (similar to combining words to create a new word – the name “Cara” and vehicle “Van” becomes “Caravan”).

DOLLAR – Used to convert a number in to a currency format based on the “Currency” settings applied to your specific computer.

USDOLLAR – Same as the “Dollar” function, but is independent of the local “Currency” settings applied to your computer as it will always use the “U.S. Currency” format.

EXACT – Used to compare two strings to see if they are identical.  This function uses “case-sensitivity” to determine if the compared values are exactly the same and displays a simple “Yes” or “No” based on the comparison result.

FIND – Used to find a string within a string.  This formula will return the starting position of the string searched for, is case-sensitive, and cannot use wildcard characters in the search.

SEARCH – Similar to the “Find” function, but is case-insensitive and does allow wildcard searching to find specific individual characters or sets of characters.

FIXED – Rounds a number to a specified number of decimal places, formats it in a standard numerical conventions using a period (”.”) and optional comma (”,”) then returns the formatted result as text (string).

LEFT – Used to get the first character (or characters) in a text string based on the number of characters you specify in the formula.

RIGHT – Same as the “Left” function, but returns characters from the end of the string rather than from the beginning.

LEN – Simply put, returns the length of a string in a numerical value.

LOWER – Converts all uppercase letters in a string into lowercase (skipping any characters that are not letters).

UPPER – Same as the “Lower” function, but instead converts all letters to uppercase.

MID – Used to return a set of characters (as a string) from a string based on the start position of the string and the number of characters to want to fetch.

PROPER – Used to format a string into an upper/lowercase convention following the rules of “Capitalize the first letter and any other letter that follows a non-letter character, then convert to lowercase all other letters in the string”.

REPLACE – Used to replace a character (or characters) in a string with text you specify.

REPT – Used to repeat a character (or characters) a number of times (useful in adding a series of repeated characters to an existing string – see this comment for an example of usage).

T – Used to get the text of a given value and display it if (and only if) the value is text (will display nothing if the value is a number or Boolean result).

TEXT – Used to convert a given (numeric) value into text and have it formatted based on a specific number format (currency, decimal, date, etc.).

VALUE – Similar to the “Text” function, this function will take a string that represents a number and convert it to a number (currency, decimal, date, etc.).

TRIM – Used to format text into a proper “sentence” format that will strip out all spaces except for single spaces between words.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s