1,804 articles and 14,828 comments as of Wednesday, May 11th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Monday, October 6, 2008

EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)

The Overview

So far, in the "Text and Data" set of formulas, we covered the following functions:

This time, we’ll continue on by exploring a few more, with some simple examples on how we can use them in our formulas.

Two

Getting started

To start, let’s look at our two new functions:

CHAR() – function is used to convert a number into it’s ASCII text equivalent (numbers in the range of 1 through 255).

CODE() – function acts as the opposite of "CHAR()" in that it converts a character into it’s corresponding numeric code.

 

Examples:

=CHAR(24) will return

=CODE("©") will return 169

Each of these can be useful in converting exported/imported data from external programs that use symbols (I believe some that use the "dBASE" format tend to do this).  Personally, I don’t generally work with programs that deal with data like this (all my data is "human-readable"), but if needed, these functions will give you the ability to convert back-and-forth if the requirement exists.

As an example though, let’s take a look at a possible Use-Case by examining a need by a fictitious advertising company.

Case Study

The advertising company, "Bob Loblaw’s Big House of Mad Ads", needs to keep up a list of all their clients, along with "Catch-Phrases" used by them in their ad campaigns.

One client, "Mom’s Old Fashioned Baked Goods", recently started sponsoring an outreach program to local teens with a series of bake sales targeted to aid in fundraising endeavors by the teen’s in after-school activities. Working with the ad company, they came up with the catch-phrase: "Roll in the Dough TM" to help inspire the teens participating in the fundraisers.

Chip Loblaw, needs to create a list in their company portal that contains the client name and their "Trademarked" catch-phrase.

In MS Word, it’s fairly simple to just use the "Insert" menu to find the "Trademark" symbol (TM), or you can just use the keyboard shortcut of "Alt+Ctrl+t" (although for me at least, I don’t have every single shortcut memorized).  So, it can be as easy as simply creating your list in Word, then "copy/paste" into a list within SharePoint since special symbols will copy over.

In this scenario however, let’s see what we can do with a calculated column to handle this for us.

Since we need the "Trademark" symbol, let’s insert one into an Excel spreadsheet so we can see how we can work with it.

In Excel, click "Insert > Symbol":


Calculated Column - Dessie Lunsford

In the window that open, click on the "Special Characters" tab then click on the "Trademark" entry and then the "Insert" button at the bottom, then close the window:

Calculated Column - Dessie Lunsford

This adds in the TM symbol to our sheet:

Calculated Column - Dessie Lunsford

Now that we have our symbol, let’s see what its numerical value is.  We do this by means of the "CODE()" function.

In our spreadsheet, in Cell B2, enter in the following formula:

=CODE(A1)

Calculated Column - Dessie Lunsford

This should return the number 153.

Next, we’ll add in our phrase and combine it with the trademark symbol using the "CHAR()" function.

In Cell C1, type in the text "Roll in the Dough".
In Cell D1, enter in the formula: =C1&" "&CHAR(B1)

Calculated Column - Dessie Lunsford

Alternatively, you could just add in the number directly to the formula instead as:

=C1&" "&CHAR(153)

(The above yields the same results of "Roll in the DoughTM", and is how we’ll do it in SharePoint)

We now have the catch-phrase and symbol joined, displaying the result of "Roll in the Dough TM".

Within SharePoint (per the requirement of our buddy "Chip"), we’ll illustrate this through a custom list called "Client List".

In SharePoint, create a custom list (Site Actions > Create > Custom List > Name it "Client List").

Once created, open the list settings and rename the "Title" column to "Company Name". 

Next, create a new column called "Catch Phrase" leaving all defaults ("Single line of text", etc.).

After these, create another new column called "Trademark", make it a "Calculated" type, and enter in the following formula:

=[Catch Phrase]&" "&CHAR(153)

Calculated Column - Dessie Lunsford

The last step is to modify the default view of the list to only show "Company Name" and "Trademark".

Once this is complete, we can enter in the information for "Mom’s Old Fashioned Baked Goods":

Calculated Column - Dessie Lunsford

Upon saving, this will display as:

Calculated Column - Dessie Lunsford

So, Chip now has the start of his list in which he can compile a complete archive of all their clients and catch-phrases.

Summary

Is there a better approach to this specific example?  Possibly.  We could have simply copied in the special symbol directly into our formula (since SharePoint does allow for it to be pasted in directly) making it slightly smaller:

=[Catch Phrase]&" TM"

This may be the approach you want to take since it’s more straight-forward and doesn’t rely on any extra functions to make it work, but in an attempt to come up with a "working" example of how to use both functions (since I never have to deal with the numerical equivalents of ASCII characters), this seemed like the simplest way to walkthrough a real-world example (so in the end, choose the approach that works best for you).

Next time, we’ll be continuing on by looking at three more functions in the "Text and Data" set that all aid in formatting text (LOWER, UPPER, and PROPER).

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.

View all entries in this series: Dessie Lunsford-Logic Formulas»
Entries in this series:
  1. Taming the Elusive "Calculated Column"
  2. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Task List
  3. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part II)
  4. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part III)
  5. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part IV)
  6. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Text and Data
  7. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part I)
  8. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part II-I)
  9. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-II)
  10. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-III)
  11. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
  12. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part IV)
  13. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)
  14. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)
  15. Taming the Elusive “Calculated Column” – Lookup and Reference
  16. Taming the Elusive “Calculated Column” - Logic
  17. Taming the Elusive “Calculated Column” - Logic - Part 1
  18. Taming the Elusive “Calculated Column” - Logic - Part 2
  19. Taming the Elusive “Calculated Column” - Logic - Part 3
  20. Taming the Elusive “Calculated Column” - Logic - Part 4
  21. Taming the Elusive “Calculated Column” - Logic - Part 5
  22. Taming the Elusive “Calculated Column” - Logic - Part 7
  23. Taming the Elusive “Calculated Column” - Logic - Part 6
  24. Taming the Elusive “Calculated Column” - Logic - Part 8
  25. Taming the Elusive “Calculated Column” - Logic - Part 9
  26. Taming the Elusive “Calculated Column” - Logic - Part 10
  27. Taming the Elusive “Calculated Column” - Date and Time - Part 1
  28. Taming the Elusive “Calculated Column” - Date and Time - Part 2
  29. Taming the Elusive “Calculated Column” - Date and Time - Part 3
  30. Taming the Elusive “Calculated Column” - Date and Time - Part 4
  31. Taming the Elusive “Calculated Column” - Referencing Multiple Lines of Text Column
  32. Taming the Elusive “Calculated Column” – Date and Time – Part 5
  33. Taming the Elusive “Calculated Column” – Date and Time – Part 6
  34. Taming the Elusive “Calculated Column” – Date and Time – Part 7
  35. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 1
  36. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 2
  37. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 3
 

Please Join the Discussion

8 Responses to “EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)”
  1. Kai Viskari says:

    Suggestion for future post: Is it possible to have Totals for a calculated column?

    I have two numerical columns which I sum in a third column using Calculated column.

    Editing the view, I can have Totals in top of two original columns but the Totals is not available for the Calculated column. The calculation returns a number but it is not available in Totals.

  2. Celeste Howden says:

    Your artciles are fantastic, I can actually understand them, thanks!

    I think I need to total a column as well. I have set up a time sheet which totals the hours worked by person and I can see monthly totals in a view grouped by date.

    Now I am trying to calculate a time in lieu balance based on a total per person (hours worked minus hours due); so I figure I need a total of the hours worked in a column, not just in the view.

    Another complexity is that each person has a different number of hours due (part timers)

    Any assistance would be most appreciated!

    Many thanks
    Celeste

  3. Kai and Celeste,

    I’m working on an idea that uses folders and a “Datasheet” view to see what the totals are for the calculated column.

    Given the specifics you mentioned (Celeste), I’m working up a basic “Time Sheet” list that will allow you to view each employee individualy and see their total hours (worked, hours due, deviance between the two with summed totals). This will use a “Datasheet” view to see the totals since through a standard view, you cant sum the totals on a calculated column (as you’ve seen).

    Once I get it worked out, I’ll see about getting it up as a new article.

    - Dessie

  4. Celeste Howden says:

    Thanks Dessie,
    Sounds terrific and much appreciated!
    I will look forward to it
    Cheers, Celeste

  5. dev says:

    Hi,

    Thanks for the article.

    I’ve created a calculated column and everything seems to be working fine. however when i save the site as a template and create a new site using the template i get an error informing me that the formula referers to a non existant column. i think this is caused by the fact that the calculated column gets created before some of the other “Required columns”. is there a work around or a fix that takes care of this?

    Cheers,
    Dev

  6. Dev,
    Can you post a simple schema of what the list entails? What columns are on the list, what is the formula in the calulated column, are you using any “Site Columns” on the list, and if so, a description of each?

    I’ve created several lists that included calculated columns that I’ve saved as templates (including their site’s as site templates), but haven’t ever come across this issue.

    If you can post back with a sample, I can try and duplicate the problem and see if I can find out what’s causing it.

    - Dessie

  7. Celeste,
    I’m still working on the list I mentioned. I’ll get something posted as soon as I have a chance to finish it.

    - Dessie

  8. celeste says:

    Thanks Dessie,
    I am still interested in the possible solutions,
    Cheers
    Celeste


Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!