Weekly Newsletter Signup



• Upcoming
  Live Online Workshops

  • SharePoint Charts and Graphs: Part 01 (Google and Yahoo APIs)
  • SharePoint Charts and Graphs: Part 02 (Sparklines)
  • SharePoint Charts and Graphs: Part 03 (Gantt Charts)
  • Create a Master Calendar in SharePoint
  • jQuery Animations in SharePoint
  • SharePoint WSS Dashboards: No Code Required
  • Become Your Company's SharePoint SuperStar!

• Ongoing Series

  • Article Archives (1000+)
    All Authors
  • This Week in SharePoint with Joel
    Joel Oleson
  • jQuery for Everyone
    Paul Grenier
  • The Calculated Column
    Dessie Lunsford
  • The Data View Web Part
    Laura Rogers
  • Stump the Panel: Q&A
  • Subscribe to the Weekly Newsletter
  • SPC09 - Live Media Streams

• Authors

  • Dessie Lunsford
  • Laura Rogers
  • Lee Reed
  • Mark Miller
  • Jim Bob Howard
  • Paul Galvin
  • Paul Grenier
  • Toni Frankola
  • Woody Windischman

• SharePoint Resources

  • Bits of SharePoint
  • Codeless Solutions for SharePoint
  • Get the Point
  • Innovation Matters
  • Laura Rogers
  • Path to SharePoint
  • Paul Galvin
  • SharePoint 911
  • SharePoint Blank
  • SharePoint Blogs at SharePointU
  • SharePoint Forums at SharePointU
  • SharePoint Screencasts
  • SharePoint Use Cases
  • Social Media Talk Tweets
  • The Sanity Point
  • The Weekly Newsletter  

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 (http://office.microsoft.com/en-us/sharepointtechnology/CH101032701033.aspx).

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.

 

Those of you familiar with working in Excel and creating formulas for your spreadsheets may recognize many of these (again, another reason why I like to model many of my formulas in Excel first, then translate them to SharePoint), but you’ll also notice that many of the formulas available in Excel are missing from this list.  What this means is that we’ll have to figure out how to use this limited set in order to produce the results we want…which is exactly what we’ll be doing in coming articles on this subject.

As we dive into exercises with these formulas, we’ll see how you can use each of these formulas (in addition to others) together in creative ways to do just about anything.

Till next time…

- Dessie 

Suggestions for future posts on calculated columns are always welcome, and if 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.

Dessie Lunsford
Points-of-Sharing
“I enjoy figuring out how to get my users to understand what they can do with SharePoint, and “how to do it” – this is probably the biggest hurdle my users have to face.”

---

Notify me of comments to this article:
E-Mail:

---

Comments