Overview

In this article, we’re going to look at how to transform existing text on a list in order to format it into a more consistent manner.

Typically, you may have many different users entering in form information onto your lists.  Because of this, it’s very common for each user to enter in the text on the individual form fields differently using non-standard formats.

Some of the more common examples would be:

1 – URL’s (web addresses) – browsers are by nature case-insensitive when its comes to the address for a particular site, but its human-nature to substitute in our own linguistic nuances when conveying information, so it’s extremely common to see an address of “http://www.ThisIsMySite.com”, when in all actuality the address could be “http://www.thisismysite.com”, “http://www.THISISMYSITE.com”, or even “http://www.tHiSiSmYsItE.com”.  This common occurrence is due to how the brain attempts to find logical patterns and meaning within information (hence the reason why most people capitalize the first letter of each word in a URL).

Excerpt from http://www.saps.gov.za/docs_publs/publications/journal/june04/face.htm:

Read through the following paragraph quickly without over-examination of the words.

“The phaomnnehil pweor of the hmuan mnid

Aorccdrnig to rscheearch at Cmabrigde Uinervtisy, it deosn’t mttaer in waht oredr the lteers in a wrod are, the olny iprmoetnt tihng is taht the frist and lsat ltteer be at the rghit pclae. The rset can be atotal mses and you can sitll raed it wouthit a prbelm. Tihs is bcuseae the huamn mnid deos not raed ervey lteter by istlef, but the wrod as a wlohe. Amzanig huh?

If you are like most people, your image smarts shuffled the letters without much effort. Your visual intelligence searched for patterns and completed meanings. The result? Your understanding of the individual words and the paragraph as a whole was as clear as if every letter had been correct in the first place.” – 2003. Judy Piatkus, London

2 – Email Addresses – although technically, these are case-sensitive in the “local-part” of the address (i.e. [email protected] is not the same as [email protected]), because of interoperability, most (if not all) ISP’s treat any form of a user’s name in the address as equivalents (jsmith = jSmith = JSmith = JSMITH).  As a guideline however, all email address assigned should be in a complete lowercase format in order to avoid any confusion (see RFC2821 section 2.4), unless a requirement exists for a specific SMTP server for the address to be formatted differently (all uppercase, first letter capitalized, etc.)

3 – Usernames (in Active Directory) – although you can create usernames using a format that includes case (i.e. John Smith), usernames in Active Directory are not case-sensitive (jsmith = JSmith).  A common action by users is to enter in their name using a “Proper” format when logging in, but is unnecessary as Windows will authenticate them regardless of the format used when entering in the name.

Getting Started

Because of these three common occurrences, many users will enter in information on lists using a mix of formatting types, which can lead to inconsistencies in how the list items appear.

To get around this and provide for a way for a list administrator to “clean-up” some of this erroneous data, we can use a technique involving a calculated column to first reformat, or “transform“, the list data into the “text-case” we want, then replace the original text through a data view.

First though, let’s take a look at the three SharePoint functions that we can use in this text-transformation process:

LOWER() – converts all uppercase letters in a text string to lowercase.

UPPER() – converts all text in a string to uppercase.

PROPER() – capitalizes the first letter in a string word, then converts the remaining characters in the string to lowercase.  If the string has more than one “set” of characters (word), it looks for any non-letter characters and uses it as a separator for the next word.  After the separator, it again capitalizes the first character of the next “set” of characters (word), and then converts the remaining characters in the set to lowercase (literally, this will capitalize the first character of each word in a sentence).

Examples of the above three function would be:

Text: “John”

Formula: LOWER(”John”)

Result: “john”

Text: “john”

Formula: UPPER(”john”)

Result: “JOHN”

Text: “look at How this text gets TRANSFORMED”

Formula: PROPER(”look at How this text gets TRANSFORMED”)

Result: “Look At How This Text Gets Transformed”

Using these, lets look at an example of a SharePoint “Contacts” list to see how we can go back and clean-up entries that were entered in non-standard formats.

Case Study

In SharePoint, create a “Contacts” list (”Site Actions > Create > ‘Communications’ column > Contacts”) called “Contacts”.

Once created, modify the default view to display only the following columns:

“Last Name”

“First Name”

“E-mail Address”

“Web Page”

Create several new items on the list (filling in only the information listed in the view), but use a variety of upper/lower-case variations in the “E-mail Address” and “Web Page” fields.

Looking at these entries, there’s nothing “technically” wrong with how they appear, but in order to keep consistency throughout the list, we’ll want to format all of the records in the same fashion using the style of the “Edison Smithington” contact as our target goal.

To accomplish this, we’re going to run through a few steps.

Steps

First, let’s look at the format of the email address for “Edison”.  It’s in an all lowercase format, so based on the definitions of the three functions I gave at the beginning, the “LOWER()” function is what we’ll be using.

Second, we’ll create a calculated column and perform the text-transformation using our formula on the “E-Mail Address” column.

Third, we’ll use a technique designed for Excel to copy our newly transformed text back into the original column to update all the records simultaneously and apply the change.

This technique is described at http://support.microsoft.com/kb/263580, but we’ll have to modify it slightly to work in SharePoint.

Since we’ve already decided on the function to use (”LOWER()”), we can now move on and create our calculated column.

In the “Contacts” list, create a new column called “TextTrans”, make it a “Calculated” type, enter in the following formula, and leave the checkbox marked for “Add to default view”:

=LOWER([E-mail Address])

Once saved, you should see the new column displayed on the list with the email addresses transformed into the proper format (converted to lowercase).

In order to update the actual “E-mail Address” column with the newly transformed text, we’re going to switch to “Edit in Datasheet” (Actions > Edit in Datasheet) and simply copy the text from the “TextTrans” column and paste it into the “E-Mail Address” column.

Switch to “Edit in Datasheet”.

This will switch us to the “Datasheet” view.

In the dataview, “Left-click and hold” on the first cell of the “TextTrans” column (not the column title, but the first email address), then drag down until you have selected each of the 4 cells containing the transformed email address:

Once you have them all selected, “Copy” what you have selected (”Right-click” and choose “Copy”, or use the keyboard shortcut “Ctrl+C”).

Next, select the 4 cells in the original “E-mail Address” column using the same technique listed above (”Left-click and hold” then drag down).  Once you have them selected, “Paste” in the contents of the “TextTrans” column (what you currently have on the clipboard), to overwrite the existing content.

Once you have pasted in the new formatted email addresses, switch back to the “Standard View” (from the “Actions” menu”) to see the updated results (they appear in the data view as well, but this is our verification step).

(Animation showing the process)

Summary

As you can see, the process is actually pretty simple, and although I’ve only highlighted the “LOWER()” function in this example, you can use the other two in a similar fashion to reformat other columns data to adhere to whatever standards you may need for keeping your data consistent.

For the other two functions (”UPPER()”, and “LOWER()”), examples of these would be (no screenshots needed for these – the above example should be enough to get you going):

• Use the same technique on both the “First Name” and “Last Name” columns with the “PROPER()” function to reformat the names in a standard “First-letter capitalized” fashion to catch any case-typing errors.  This will transform whatever is entered into each of these into a standard of the first letter being capitalized and the remaining letters converted to lowercase.


• Use the same technique again on the “Company” column with the “UPPER()” function to transform the company names to all uppercase (this may, or may not be appropriate for your specific needs, but will server as an example of how to use the function since it acts in a similar manner to the “LOWER()” function).

Next time, we’ll be continuing on by looking at five more functions in the “Text and Data” set that all aid in validating, formatting, and modifying text (ASC, CLEAN, EXACT, REPT, T).

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.