
Overview
Last time, we modified the default "Contacts List" in order to see what other functionality we could introduce that could extend its default capabilities.
This is a continuation of the previous article "" in which we created a contacts list called "Employee Contacts" - this post will be using the same list again.
We began by addressing the need for an easier method of capturing the email address from "Internal" contacts (looking at this list as an "Employee Contacts" list rather than "All Contacts") by developing a calculated column that dynamically builds the email address based on the contact’s name.
This time, we’ll be looking at an approach we can take in order to "validate" the format of items entered, specifically a phone number (a question I’ve seen asked many times).
For this initial walkthrough, we’ll be looking at two (of the numerous) standard phone number formats:
555 123 4567 (with spaces)
555-123-4567 (with dashes)
Each of these is technically a "good" phone number, but depending on your localization or "data needs", you will generally have a preference (or requirement) to format all phone numbers in a specific fashion.
For simplicities sake, we’ll use the format of "555-123-4567" as our targeted phone number format.
Getting Started
So how do we begin this? We could use "InfoPath" or a custom "coded" solution to perform the validation since we’d have the power of "Regular Expressions" at our disposal, but within SharePoint (using only the built-in tools), we have to validate list items after-the-fact.
Because of this limitation, we’re going to have to manipulate the contents of an existing column, format it as we need, then display it in a new column (essentially, a "find-and-replace" operation in the manner in which the system will allow us).
The process we’ll be using isn’t specifically a "find-and-replace", but for arguments sake, we can look at it as such in order to have a clearer understanding of just what we’re trying to accomplish.
To begin, open up Excel and enter in the following in cell A1:
Text to enter: "555 666 7777"

In cell A1, right-click and set the format as "Text" - this will make sure and keep the contents of this cell in the format we want and not treated as numbers.
This simply gets us a phone number entered in with spaces as the separator.
Next we need to decide how to "find-and-replace" each space with a dash (-).
Since the available functions for our use do not include a "FindAndReplaceEachOccuranceISpecify()" function, we’re going to have to break the string apart (similar to a "substring" operation) in order to get at each space and replace it with a dash.
In cell C1, enter in the following formula:
=TRIM(LEFT(A1,FIND(" ",A1)-1))&"-"&TRIM(MID(A1,LEN(LEFT(A1,FIND(" ",A1))),LEN(RIGHT(A1,FIND(" ",A1)))))&"-"&TRIM(RIGHT(A1,FIND(" ",A1)+1))
Once the formula is entered in, you should see a properly formatted phone number appear in cell C1:

Dissecting the formula, we can see several different functions:
3 "TRIM" functions
2 "LEFT" functions
4 "FIND" functions
1 "MID" function
2 "LEN" functions
2 "RIGHT" functions
4 "&" (Concatenate) functions
As discussed in previous articles, we will often have to use several functions in combination with each other in order to duplicate some of the functionality available in other platforms (Excel, coded solutions, etc.).
To see how this formula works, we’ll start with what each function does:
TRIM(text) - Removes all spaces from text except for single spaces between words (this is an important first step since we want to eliminate as much as possible first so our text is "clean" - this will make the "find-and-replace" more efficient).
LEFT(text, num_chars) - gets the number of characters (from the left) you specify from a given string.
FIND(find_text,within_text,start_num) - Finds the starting position of text within a string (finding where in a string a specific character or series of characters begins), and is case-sensitive.
& - Concatenates strings together (as discussed last time).
MID(text,start_num,num_chars) - Gets a specific number of characters from a string based on a specified starting point and number of characters to return.
LEN(text) - returns the number of characters in a string.
RIGHT(text,num_chars) - gets the number of characters (from the right) you specify from a given string.
So, using the above definitions, the formula steps through as:
- TRIM(remove all spaces from text)
- LEFT(starting from the leftmost part of the text in Cell A1)
- FIND(get the first occurrence of a space) - 1 (the minus 1 moves us one character to the left of the space so we don’t include the space in our selection)
- Concatenate onto the above a dash (-), followed by:
- TRIM(remove all spaces from the text)
- MID(get the length of the text from the leftmost part of the string to the first space, get the length of the text from the rightmost part of the string (moving backwards) to the first space - essentially just getting the innermost part of the text from the first space to the last)
- Concatenate onto the above a dash (-), followed by:
- TRIM(removes all spaces from the text)
- RIGHT(starting from the rightmost part of the text in Cell A1 (moving backwards))
- FIND(get the first occurrence of a space) +1 (the plus 1 moves us one character to the right so we don’t include the space in our selection)
Analysis
As complicated as the above may appear, all we’re doing is separating the string into three parts:
First part (LEFT) - from the left until a space is encountered (then move one character to the left so as to not include the space in the selection).
Second part (MID) - middle section of the string (including the space at beginning and end)
Third part (RIGHT) - from the right (moving backwards) until a space is encountered (then move one character to the right so as to not include the space in the selection).
On each piece we separate, we run the "TRIM" function to remove any leading and/or trailing spaces (mainly affecting the "MID" section), and then merge each piece back together with a dash in between each piece (this serves as our custom version of the "find-and-replace" operation).
So, with the string "555 123 4567", we have:
First string = "555"
Second string = " 123 " (note the space before and after the "123")
Third string = "4567"
Trimming the spaces on each (although in this case the first and third strings don’t have any extra spaces at their respective beginning or ending, it’s a good idea to still perform the operation anyways since someone may inadvertently add in an extra space in the list itself) will now make each string "clean" and prepped to be concatenated back together. Concatenating them (after "trimming") will now produce our finalized end result string of:
"555-123-4567"
To get this working on our "Employee Contacts" list in SharePoint, we need to create our calculated column and modify the formula accordingly to use the phone number field we want to format.
In SharePoint, go to the contacts list and create a new column called "Home Phone Number". Make the new column a "Calculated" type, and paste in the formula from excel making the following changes:
We’re going to be calculating the phone number entered in the "Home Phone" field, but using our new field for the view.
Formula in Excel:
=TRIM(LEFT(A1,FIND(" ",A1)-1))&"-"&TRIM(MID(A1,LEN(LEFT(A1,FIND(" ",A1))),LEN(RIGHT(A1,FIND(" ",A1)))))&"-"&TRIM(RIGHT(A1,FIND(" ",A1)+1))
Replace each occurrence of "A1" with "[Home Phone]", making sure that you do use the brackets [] around the name of the column.
Formula in SharePoint:
=TRIM(LEFT([Home Phone],FIND(" ",[Home Phone])-1))&"-"&TRIM(MID([Home Phone],LEN(LEFT([Home Phone],FIND(" ",[Home Phone]))),LEN(RIGHT([Home Phone],FIND(" ",[Home Phone])))))&"-"&TRIM(RIGHT([Home Phone],FIND(" ",[Home Phone])+1))

Hit "OK" to save the column, and then create a new item on the list:
(All you need for this test is to enter in the name of the contact and their "Home Phone" number in the format of "555 123 4567" - make sure and use spaces in the number)

Once saved, you should see our calculated column displaying the phone number in the proper format (Note - I’ve stripped down the view for readability to only have the contact’s name and phone numbers visible):

All we have to do now is modify our view to use our column "Home Phone Number" instead of the default "Home Phone" for display (modify the view and uncheck the column "Home Phone").

Editing the item will still be the same in that you’ll actually modify the "Home Phone" field, but the new column being displayed in the view will add more consistency in how items are displayed.
Summary
What we’ve done here is take in account for only one of several different phone formats commonly used, and as a result will only work if numbers are entered in using the "space" in between each set of numbers ("555 123 4567"), so this is far from a complete solution as it currently stands (we’ll remedy this in upcoming posts on this topic).
The hope is that (although incomplete in it’s current state) the process I’ve discussed here will shed some light on how to begin tackling problems such as these using nothing more than out-of-the-box functionality…and a little creativity.
Next time, I’ll be continuing on with this same topic as we introduce a second phone format (dotted separation "555.123.4567") and how to format it using a different approach that more resembles a traditional "Find-and-Replace".
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.
Dessie Lunsford
Points-of-Sharing