Overview

Last time, we looked at 3 functions for formatting the case of text (working with uppercase and lowercase letters, as well as the “Proper” format of first letter capitalized only in a word).

In this article, we’re going to look at a few more tools we have in the “Text and Data” set of functions that can give you increased options in how to validate and transform data in your lists.

The five functions we’ll be looking at are:

*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 before and after conversion?)

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.

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.

REPT – Used to repeat a character (or characters) a number of times (useful in adding a series of repeated characters to an existing string.

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 non-text type – i.e. number or Boolean result).

*I wont be covering examples of the “ASC()” function (other than the example given in its definition) since its usage requires a system that can render the “Double-wide” characters (international settings), but suffice it to say that the function can be useful during language conversions to deal with the character sets used.

For the remaining 4 functions, let’s look at some examples of their usage:

Getting Started

The “CLEAN()” function is useful when moving data from one type of system to another in that it allows you to remove any characters that do not directly represent a written symbol (“Non-Printing”, or “Control” characters), but rather, represents a specific action or formatting on the data itself. Examples of this would be “line feeds” and “tabs” (or anything that doesn’t fall in the Decimal 32-127 and 128-255 ASCII ranges – “Printable” and “ASCII Extended” respectively – http://www.ascii-code.com).

To see how this works (using the example of “tabs“), open a blank text document (Notepad, WordPad, etc.), and enter in the numbers 1-5 separated by tabs.

In SharePoint, create a new “Custom List” named “Sample Calculations” (these will be simple examples, so if you want, you can use any list since we’ll be creating the columns as we go).

In the list, create a new column named “Raw Text“, make it a “Single line of text” type, leave the rest as default and click “OK”.

Create a new item on the list and paste in the tab-separated list of numbers from your text document.

Once saved, you’ll see the entry containing the numbers separated with spacing (although appearing like single spaces, it is in fact tab-separated).

Create another new column called “Cleaned Text“, make it a “Calculated” type and paste in the following formula (leave the rest as default – “Single line of text” for returned type):

=CLEAN([Raw Text])

Once saved, notice the “Cleaned Text” column has stripped away all the spacing (tabs)?

You may be thinking, “Why don’t we just use the “TRIM()” function to remove the extra spaces?” Although this will remove any irregular spacing in text, the “Tab” character is not equal to the “Space” character, so it would be ignored in the “TRIM()” function.

Next, create another item on the list, but replace the “tabs” with a series of spaces that equal the same spacing the tabs created.

Visually, they do appear the same, but the background information (code) that renders the information is very different.

Upon saving, notice how the spacing remains intact?

This is because although visually appearing the same, the “space” character is a printable character, and will be rendered literally.

Notice how SharePoint treats spaces compared to tabs – the spaces are treated literally, whereas the tabs get “trimmed” down some on the visual display to preserve space (because of this internal “trimming”, attempting to “copy” it from the list and paste it back into another document will result in it being space-separated instead of tab-separated).

Looking at the source (viewing the raw html) of the page you can see that the “tab-separated” entry will appear as:

And the result after the calculation:

Looking at our test comparison of “space-separated”, we can see it as:

(The calculated column also displays the exact same html).

So, even though it “appears” to be not in a “tab-separated” format in the visual presentation, it is still tab-separated, so the calculation will indeed work.

The next function “EXACT()”, is used to compare strings (text) to determine if they are the same and uses case-sensitivity in its comparison. Because of this we should observe the following:

String1 = “Ice Cream”
String2 = “ice Cream”
String1 is not = to String2

Because of case-sensitivity, these two are not identical and will result in “No”.

=EXACT(”Ice Cream”, “ice Cream”)
Result = No

To test this function, we’ll use the example above (”Ice Cream”) in our SharePoint list.

Create two columns, called “Text1” and “Text2“, making them both “Single line of text” leaving all as default.

Create another new column called “Comparison“, make it a “Calculated” type and enter in the following formula (leave as “Single line of text” for return type):

=EXACT([Text1],[Text2])

As soon as you save the new column and return to the view of the list, you’ll notice that the “Comparison” column already displays “Yes” for any existing items. This occurs because in their current state, both the “Text1″ and “Text2″ columns contain the same thing (nothing), so the “EXACT()” formula will return true.

Once created, add a new item to the list and add in the following:

“Text1″ column: “Ice Cream”
“Text2″ column: “ice Cream”

Once saved, the “Comparison” column will evaluate the two fields and return the result of whether or not they contain the same information, which in this case, is “No” since they are not identical based on the rules of “Case-Sensitivity” (note – for readability, I removed the previous two columns from the view and filtered out the results to only display those with a title containing “Comparison”).

Modifying the text in “Text2″ to make the first letter capitalized would change the result to “Yes”, but if we wanted to make allowances for case-insensitivity for future entries, we can modify our formula to include the use of the “PROPER()” function (detailed last time) to make each field in the same case before comparing them.

Formula:
=EXACT(PROPER(”Ice Cream”), PROPER(”ice Cream”))

Becomes:
=EXACT(”Ice Cream”, “Ice Cream”)

Result = Yes

To do this in our list, modify the formula in the “Comparison” column as:

=EXACT(PROPER(Text1),PROPER(Text2))

After saving, go back and view the list to see that the “Comparison” field now displays “Yes” as its result since it is now first converting each field into a proper format before comparing.

Obviously the above may not work for all scenarios since you may want differences (some things should not be capitalized for example), but in some cases the data you may be working with is coming from a different system and may require a “preparation” before you can work with it.

In my organization for example, we have a system that will only accept capitalized input, so it’s extremely common for users to forget to switch back from “Caps Lock” when exiting the system – causing them to type in all capital letters for some time before realizing it. The data we extract from this system has to be prepped first by using something like the “PROPER()” technique, before we can use it in SharePoint.

The next function we’ll look at is the “REPT()” function. This function simply displays a string (character or series of characters) a specified number of times.

Examples:

=REPT(”*”,10)
Result = **********
(Literally: “Display the asterisk character 10 times”)

=REPT(”0″,5)&”Alpha”
Result = 00000Alpha
(Literally: “Display the zero character 5 times followed by the text ‘Alpha’)

One approach you can take with this is to build out your own type of ID numbering system that follows a specific pattern, or total character count (mentioned in a previous article comment).

The idea is to have for example, a 10 digit number as the ID while allowing for shorter numbers that get entered to be formatted with the 10-digit convention.

This example uses a process of checking the value entered to see if it’s less than 10 digits, and if so, adds in the required amount of “zero’s” to pad the left (start) of the number to make it 10 digits in length. If the number entered is indeed 10 digits, it will simply display the number.

The formula for this is:

=IF(LEN(Number)<10,REPT(0,(10-LEN(Number)))&Number,TEXT(Number,”0″))

This formula literally steps along as:

If the length of [Number] is less than 10, create an amount of zero’s equaling the difference between 10 and the length of [Number] then display these zero’s followed by the [Number]. If the length of [Number] is not less than 10, display the number using the “TEXT()” formatting option to display it in a number format.

Examples:

Number = 12345
Result = 0000012345

Number = 1234567890
Result = 1234567890

Moving this into SharePoint, create a new column called “Number” and make it a Number type with no decimal places (this could also be a “Text” type column since we’re not performing any mathematics on the value of the column – I just chose “Number” for this example).

Create another new column called “ItemID“, make it a calculated type and enter in the following formula:

=IF(LEN([Number])<10,REPT(0,(10-LEN([Number])))&[Number],TEXT([Number],”0″))

Once saved, if you return to the list you’ll see immediately that the “ItemID” column is now populated with 10 zero’s (note – I’ve again trimmed the view to only display the current two columns we’re working with for readability).

This occurs because the “Number” field is currently empty, so its length is (obviously) less than 10, and since the difference between its length (0) and the maximum length we determined (10) is 10 (10-0=10), the result is 10 zero’s.

Now, lets edit the item and add in a value to the “Number” column (add in any value you want as long as it’s not longer than 10 characters in length – for this example, I’ll just add in “12345″).

Once saved we’ll see the “ItemID” column update to include the value entered in the “Number” column along with our padding of zero’s to make it still equal 10 characters in length.

Note the format of the value in the “Number” column? This is because we specified it to be a “Number” type initially and it will format the entered values based on numerical convention (hence the comma). Since we’re not actually performing any mathematical calculation on the value in the column itself, we could/can go into its settings and change it from a “Number” type to a “Single line of text” type without experiencing any problems.

The next steps for this would be removing the “Number” column from the view, and possibly adding in the functionality in the formula to allow for numbers longer than 10 digits (possibly trimming off anything longer, or displaying a text message to inform users that they need to shorten it).

The last function we’ll be looking at this time is the “T()” function. This function is used to see if a specific value is text, and if it is, display the text (if its not, it wont display anything).

To use this function, you simply pass in a reference to a column and it will check to see if its value is text.

Formula:
=T([Column Name])

Looking at our standard (”Out of the box”) column types, we can see what this means:

Column types (with example names):

1. Single line of text (Col1)
2. Multiple lines of text (Col2)
3. Choice (Col3)
4. Number (Col4)
5. Currency (Col5)
6. Date and Time (Col6)
7. Lookup (Col7)
8. Yes/No (Col8)
9. Person or Group (Col9)
10. Hyperlink or picture (Col10)
11. Calculated (Col11)

Testing each of these with the “T()” function, we will see the following results:

=T([Column Name])

Using Col1, Col2, or Col3 will result in the values of those columns being displayed since they each have a return type of text (the calculation will return “True”, so it displays the text of the evaluated column).

Col4, Col5, and Col6 will all result in nothing being displayed since each of them has a return type that is not text (calculation will return “False” since Col4 is a number, Col5 is currency, and Col6 is Date/Time).

Col7 and Col9 are both *unusable since calculated columns cannot reference a “Lookup” or “Person or Group” column.

*Note – although “technically” unusable, there are, what can be considered “Hacks” or “Workarounds”, to bypass the errors you will see when attempting to make a reference to one of these types of “Calculation-Incompatible” columns.

For example, create a “Text” column then make a reference to it in your formula. Once the calculated column is created, go back and change the data type of the original “Text” column, or delete it and recreate it with a different data type – reusing the original name.

Although some of these types of techniques do work (the now infamous “Today” column trick), you can’t always rely on that approach since many times, it wont produce the results you may be expecting. Attempting to use the example mentioned above (modifying the input column data type after-the-fact) to induce usability of a “Lookup” or “Person or Group” column, in this case, still won’t work since even though the error message has been bypassed, the formula still recognizes the incoming data as incompatible.

Col8 will also be blank since a “Boolean” (yes/no, on/off, 1/0) value is not text.

Col10 although not directly usable (Calculated columns cannot directly reference a “Hyperlink” type column), it can be “forced” to be usable through the technique mentioned above (create a “text” column with the name you want, make a reference to it in your calculated column then go back and delete the “text” column and recreate it as a “Hyperlink” column). The result will be that the formula will display the “text” only of the link (not the actual hyperlink itself).

Using the same concept in the above example, but instead using the “Format URL as: Picture” option of the “Hyperlink or Picture” column type, the “T()” function will display the “Alt text” of the image.

Col11 will return only values that are in a “text” format, so if the to-be-tested calculated column value is for example, a number, the result will be blank (if it’s text, it will display the text value).

Uses of the “T()” function could include “data validation” that involves the inclusion of an “IF()” function to display “friendly” messages if the data is not-text:

Example:

=IF(ISERROR(VALUE([Column Name])),IF((T([Column Name])<>”"),[ Column Name],”Field is empty or not ‘text’-only”),”Field is empty or not ‘text’-only”)

This formula steps through as:

If the value of [Column Name] is not a number, or if the value of [Column Name] is not empty, display the value of [Column Name]. If the value of [Column Name] is a number, not blank, or anything but text, display the message “Field is empty or not ‘text’-only”.

In this case, the “VALUE()” function looks to see if the data in the [Column Name] column is representing a number, and if so, converts it to that number. Because the function returns an error (#VALUE!) if it’s not a number (number being a constant number, date, or time format), we have to wrap it in an “ISERROR()” function to remove the error from being displayed. Since we also want to decide how to handle it if an error is found, we wrap the “ISERROR” function in an “IF()” function that proceeds to further calculations depending on whether or not there was an error. If it does not find an error (the value is a numerical format), we display a friendly message to the user reminding them to only enter in text. If it does find an error (meaning the value in the column is not in a numerical format), we then test to see if the value is empty. If it’s not empty, we can safely display the value (which will be text). If it is empty, we display a friendly message to the user reminding them that they can only enter in text and that the field cannot be empty.

There’s obviously other ways of handling parts of this (make the column “Required” so we can omit the “check for blank” step), but the idea here is to show different approaches you can take (many ways of “Skinning-The-Cat that is SharePoint”), to give you the most flexibility available.

Summary

Next time we’ll be concluding the series on “Text and Data” covering the last 4 functions in the set by discussing “DOLLAR“, “USDOLLAR“, “FIXED“, and “VALUE“.

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the “real-world” problems that we each face day to day, so if you have an example, an idea you want to explore, or a “Can this be done with a Calculated Column?” question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.