Overview

In the previous two articles (Part II-I and Part II-II), we looked at an approach to "Data Validation" in an "Employee Contacts" list by creating calculated columns that perform "Find-and-Replace" operations on a phone number in order to reformat them into a dash-separated style (555-666-7777).

Getting Started

This time, we’re going to combine both of those methods into a model that will allow us to check for two formats ("555 666 777" and "555.666.7777") and reformat them both to our target format of "555-666-7777", with error checking.

If you haven’t read them yet, I’d encourage you to review both previous articles to get an understanding of how the formulas work in order to better understand how we’re going to modify them this time.

In order to be able to catch both types of formats, we need to take on a new approach that uses multiple calculated columns that each performs a specific function before displaying our final result of the targeted format ("555-666-7777").

To begin, open Excel and create a new sheet with the following columns:

Our first column ("Home Phone") is where we’ll be entering in our test phone numbers in each of 3 formats:

"555 666 7777" – (space separated)
"555.666.7777" – (dot separated)
"555-666-7777" – (dash separated)

For the second column ("WithSpaces"), enter in the following formula:

=IF(ISERROR(TRIM(LEFT(A2,FIND(" ",A2)-1))&"-"&TRIM(MID(A2,LEN(LEFT(A2,FIND(" ",A2))),LEN(RIGHT(A2,FIND(" ",A2)))))&"-"&TRIM(RIGHT(A2,FIND(" ",A2)+1))),C2,TRIM(LEFT(A2,FIND(" ",A2)-1))&"-"&TRIM(MID(A2,LEN(LEFT(A2,FIND(" ",A2))),LEN(RIGHT(A2,FIND(" ",A2)))))&"-"&TRIM(RIGHT(A2,FIND(" ",A2)+1)))

This is the same formula as detailed in the first article with the addition of an "Error Check" in the beginning.

The "ISERROR" function simply attempts to evaluate the formula:

TRIM(LEFT(A2,FIND(" ",A2)-1))&"-"&TRIM(MID(A2,LEN(LEFT(A2,FIND(" ",A2))),LEN(RIGHT(A2,FIND(" ",A2)))))&"-"&TRIM(RIGHT(A2,FIND(" ",A2)+1))

If it does not find any errors it goes ahead and performs the calculation to reformat the space-separated phone number into a dash-separated format and displays it in Cell B2.

If it does find an error (value is not in a space-separated format), it calls Cell C2, which happens to have another formula in it (that we’ll now setup).

In Cell C2 (our third column called "WithDots"), enter in the following formula:

=IF(ISERROR(REPLACE(REPLACE(A2,SEARCH(".",A2),1," "),SEARCH(".",REPLACE(A2,SEARCH(".",A2),1," ")),1," ")),A2,REPLACE(REPLACE(A2,SEARCH(".",A2),1," "),SEARCH(".",REPLACE(A2,SEARCH(".",A2),1," ")),1," "))

This is the formula for checking if the value in Cell A1 is in a dot-separated format.  The formula is similar to the version described in the previous article, but instead of going ahead and replacing the dot with a dash, we replace it with a space (explained below) – and like the previous column ("WithSpaces"), we also include error checking.

As in the previous column, the "ISERROR" function simply attempts to evaluate the formula:

REPLACE(REPLACE(A2,SEARCH(".",A2),1," "),SEARCH(".",REPLACE(A2,SEARCH(".",A2),1," ")),1," ")

If it does not find any errors it goes ahead and performs the calculation to reformat the dot-separated phone number into a space-separated format and displays it in Cell C2.

The reason why we’re replacing the dots with spaces is because the "dot" character is a "Special Character" in formulas that can be used like a "Wildcard" to "Find Any Character" (this functionality doesn’t really work in SharePoint, but since SharePoint calculated columns are based on Excel, problems will occur if trying to search on the "dot").  Since the limitations in available functions (in SharePoint) does not allow us to "Escape" (treat as a literal and not a "Special" character) characters, and we already have the calculated column to handle space-separated formats, we simply convert the dot-separated phone number into a space-separated number then format it to dash-separated.

If it does find an error (value is not in a dot-separated format), it simply displays the value of Cell A2 since it is presumed to be in the correct format of "555-666-7777" (this presumption is based on the fact that the space-separated format is already handled by Cell B2 and if it’s not in a dot-separated format and being handled by the current Cell C2, then it must be in the correct format already – NOTE: for sake of argument, in our current setup of only being able to handle three formats, we’re always assuming that the phone number will be in one of the three). 

Our next column "DotsToSpaces", is created to handle reformatting the "WithDots" column into a space-separated format.  We need this column because we can’t reuse our original "WithSpaces" column to also reformat the "WithDots" column since there’s no way of passing in a second column to format (which is ok because our final column will help to explain this).

So, in the "DotsToSpaces" column, enter in the following formula:

=IF(ISERROR(TRIM(LEFT(C2,FIND(" ",C2)-1))&"-"&TRIM(MID(C2,LEN(LEFT(C2,FIND(" ",C2))),LEN(RIGHT(C2,FIND(" ",C2)))))&"-"&TRIM(RIGHT(C2,FIND(" ",C2)+1))),"",TRIM(LEFT(C2,FIND(" ",C2)-1))&"-"&TRIM(MID(C2,LEN(LEFT(C2,FIND(" ",C2))),LEN(RIGHT(C2,FIND(" ",C2)))))&"-"&TRIM(RIGHT(C2,FIND(" ",C2)+1)))

Notice how the formula is exactly the same as the "WithSpaces" column, but with a different Cell reference (Cell C2 instead of Cell A2)?

Question?

Now that we have everything setup to format the phone number, how do we combine the columns together and display the results in a single column instead of the three new one’s we just created?

You may be thinking "Can’t we just merge all the formulas together into one huge one?"  Unfortunately (or rather, fortunately in our case since the length of the formula would be headache-inducing to troubleshoot and debug), we’re precluded from doing this due to the limited number of nested function we can use (Excel can use 7, SharePoint can use 8).  So, by breaking the functionality into multiple columns that each has less than the maximum number of nested functions contained within them, we can use a fourth and final column that references each of them in order to produce a final result.

Answer:

Our last column called "Home Phone #" will in effect, merge all of the calculations in the other columns together in order to check which format the phone number is in, reformat it appropriately and then display the result.

So, in our final column, enter in the following formula:

=IF(ISNUMBER(FIND("-",A2)),A2,IF(ISNUMBER(FIND(" ",A2)),B2,IF(ISNUMBER(FIND(".",A2)),D2,"none")))

Looking at this formula, we can see several different things taking place:

3 "IF" functions
3 "ISNUMBER" functions
3 "FIND" functions

Dissecting this formula, we have:

IF(logical_test,value_if_true,value_if_false) – Conditional statement that returns one value if the condition you are checking is true and another value if false.

ISNUMBER(value) – True/False formula that returns true if a given value is a number and false if it is not a number.

FIND(find_text,within_text,start_num) – Finds the starting position of text within text (finding where in a string a specific character or series of characters begins), and is case-sensitive.

Analysis

Based on the above definitions, for our formula we have:

=IF(ISNUMBER(FIND("-",A2)),A2,IF(ISNUMBER(FIND(" ",A2)),B2,IF(ISNUMBER(FIND(".",A2)),D2,"none")))

IF
1. Logical_test
   ISNUMBER
   1. Value
      FIND
      1. Find_text: “-”
      2. Within_text: A2
      3. Start_num: (presumed to be) 1
2. Value_if_true: A2 (Cell does contain a dash, so display its contents)
3. Value_if_false
   IF
   1. Logical Test
      ISNUMBER
      1. Value
         FIND
         1. Find_text: ” “
         2. Within_text: A2
         3. Start_num: (presumed to be) 1
   2. Value_if_true: B2 (Cell contains spaces, so call “WithSpaces” to format)
   3. Value_if_false
      IF
      1. Logical_test
         ISNUMBER
         1. Value
            FIND
            1. Find_text: “.”
            2. Within_text: A2
            3. Start_num: (presumed to be) 1
      2. Value_if_true: D2 (Cell contains dots, so call “DotsToSpaces” to format)
      3. Value_if_false: “none” (displaying the text “none” to signify that no phone number was found in Cell A2)

As described in an earlier article, what we have is called an "IF-ELSE" chain, or series of "IF" statements that get called based on the failing (evaluating to false) of a previous "IF" statement.

This is essentially (with a much cleaner view of the logic):

IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else IF (condition to check)
{Do something if above is “true”, if false, do nothing and move on}
Else
{Do Something – think of this as the “cleanup” or “catch-all” – “if none of the above, do this”}
}

In our case, we’ll be adding in each subsequent "IF" in place of the "FALSE" of each preceding "IF".

So, we can literally look at our formula as:

=IF(ISNUMBER(FIND("-",A2)),A2,IF(ISNUMBER(FIND(" ",A2)),B2,IF(ISNUMBER(FIND(".",A2)),D2,"none")))

“Try and find a dash in the value of Cell A2 and get its start position.
If this start position is a number – meaning we did actually find a dash, display the value of Cell A2.
If we didn’t find a dash, try and find a space in the value of Cell A2 and get its start position.
If this start position is a number – meaning we did actually find a space, display the value of Cell B2.
If we didn’t find a space, try and find a dot in the value of Cell A2 and get its start position.
If this start position is a number – meaning we did actually find a dot, display the value of Cell D2.
If we didn’t find a dot, display the text “none” signifying that apparently a
phone number was not even entered in the first place.”

The fun part of this is that since the values of both Cells B2 and D2 are based on formulas themselves, when our formula in the "Home Phone #" column calls each of them based on the phone number format, their respective formulas will then run and display the output back to the original column that called them – in our case, the "Home Phone #" column (neat huh?).

To test these formulas, go ahead and change the phone number in the "Home Phone" (our first column) column to be each format and see how the other columns change:

With Spaces:

With Dots:

With Dashes:

No number entered:

To get this working in SharePoint, go to our "Employee Contacts" list (same "Contacts" list as last time – refer to the first article if you need to create one).  Since we’re modifying the formulas we used previously and need to use different names for the columns, go ahead and delete the columns we created in the last two articles (skip this step if you are just now creating the list).

The two columns we had previously used were named "Home Phone Number" and "Home Phone Number 2" – you can now delete them.

Create a new column called "WithDots":

It’s easier if we create this one first since it doesn’t have any references to other calculated columns.

Make it a "Calculated" type and enter in our formula from Excel making the following modifications:

Formula in Excel:
=IF(ISERROR(REPLACE(REPLACE(A2,SEARCH(".",A2),1," "),SEARCH(".",REPLACE(A2,SEARCH(".",A2),1," ")),1," ")),A2,REPLACE(REPLACE(A2,SEARCH(".",A2),1," "),SEARCH(".",REPLACE(A2,SEARCH(".",A2),1," ")),1," "))

Replace each occurrence of "A2" with "[Home Phone]" making sure to include the brackets [] around the column name.

Formula is SharePoint:
=IF(ISERROR(REPLACE(REPLACE([Home Phone],SEARCH(".",[Home Phone]),1," "),SEARCH(".",REPLACE([Home Phone],SEARCH(".",[Home Phone]),1," ")),1," ")),[Home Phone],REPLACE(REPLACE([Home Phone],SEARCH(".",[Home Phone]),1," "),SEARCH(".",REPLACE([Home Phone],SEARCH(".",[Home Phone]),1," ")),1," "))

Next Create another column called "WithSpaces" making it a "Calculated" type and enter in our formula from Excel making the following modifications:

Formula in Excel:
=IF(ISERROR(TRIM(LEFT(A2,FIND(" ",A2)-1))&"-"&TRIM(MID(A2,LEN(LEFT(A2,FIND(" ",A2))),LEN(RIGHT(A2,FIND(" ",A2)))))&"-"&TRIM(RIGHT(A2,FIND(" ",A2)+1))),C2,TRIM(LEFT(A2,FIND(" ",A2)-1))&"-"&TRIM(MID(A2,LEN(LEFT(A2,FIND(" ",A2))),LEN(RIGHT(A2,FIND(" ",A2)))))&"-"&TRIM(RIGHT(A2,FIND(" ",A2)+1)))

Replace each occurrence of "A2" with "[Home Phone]" (using the brackets[]), and replace "C2" with "WithDots".

Formula in SharePoint:
=IF(ISERROR(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))),WithDots,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)))

Create another new column called "DotsToSpaces" making it a calculated type and enter in our formula from Excel making the following modifications:

Formula in Excel:
=IF(ISERROR(TRIM(LEFT(C2,FIND(" ",C2)-1))&"-"&TRIM(MID(C2,LEN(LEFT(C2,FIND(" ",C2))),LEN(RIGHT(C2,FIND(" ",C2)))))&"-"&TRIM(RIGHT(C2,FIND(" ",C2)+1))),"",TRIM(LEFT(C2,FIND(" ",C2)-1))&"-"&TRIM(MID(C2,LEN(LEFT(C2,FIND(" ",C2))),LEN(RIGHT(C2,FIND(" ",C2)))))&"-"&TRIM(RIGHT(C2,FIND(" ",C2)+1)))

Replace each occurrence of "C2" with "WithDots"

Formula in SharePoint:
=IF(ISERROR(TRIM(LEFT(WithDots,FIND(" ",WithDots)-1))&"-"&TRIM(MID(WithDots,LEN(LEFT(WithDots,FIND(" ",WithDots))),LEN(RIGHT(WithDots,FIND(" ",WithDots)))))&"-"&TRIM(RIGHT(WithDots,FIND(" ",WithDots)+1))),"",TRIM(LEFT(WithDots,FIND(" ",WithDots)-1))&"-"&TRIM(MID(WithDots,LEN(LEFT(WithDots,FIND(" ",WithDots))),LEN(RIGHT(WithDots,FIND(" ",WithDots)))))&"-"&TRIM(RIGHT(WithDots,FIND(" ",WithDots)+1)))

Now, create our final column called "Home Phone #" making it a calculated type and enter in the formula from Excel making the following modifications:

Formula in Excel:
=IF(ISNUMBER(FIND("-",A2)),A2,IF(ISNUMBER(FIND(" ",A2)),B2,IF(ISNUMBER(FIND(".",A2)),D2,"none")))

Replace each occurrence of "A2" with "[Home Phone]" (using brackets []), "B2" with "WithSpaces", and "D2" with "DotsToSpaces".

Formula in SharePoint:
=IF(ISNUMBER(FIND("-",[Home Phone])), [Home Phone],IF(ISNUMBER(FIND(" ",[Home Phone])),WithSpaces,IF(ISNUMBER(FIND(".",[Home Phone])),DotsToSpaces,"none")))

Once you have all the new columns created, modify the list view to show only the following columns ("Last Name", "First Name", "Home Phone", "WithDots", "WithSpaces", "DotsToSpaces", "Home Phone #"):

To test our new functionality, create a new item on the list (for this test, all you need to enter in is the name of the contact and their "Home Phone" – enter in the number as "555-123-4567"):

If everything was entered in correctly in our formulas, you should see the following:

Since we entered in our phone number in the correct format, the "Home Phone #" field simply displays the contents of the "Home Phone" field (no reformatting was necessary).

To test if the formatting works for the other phone number formats, edit the item and change the phone number to use the space-separated format ("555 123 4567"):

Notice how each calculated column displays the number and how the "Home Phone #" column displays the number reformatted to be dash-separated?

Edit the item again and change the phone number to use the dot separated format ("555.123.4567"):

Again, note the differences in how each column displays the phone number and that the "Home Phone #" column shows the number reformatted into the dash-separated format.

Edit the item one final time and remove the phone number altogether:

Note the values being displayed.  The 0’s in the "WithDots" and "WithSpaces" columns refers to the "FIND" function not finding anything so a zero is passed back as the result, and since there wasn’t anything entered in for the number, the "Home Phone #" column simply displays "none".

The last bit of cleanup is to just modify the view to use only the columns we want – in this case, just the contact name and our custom column "Home Phone #":

 

Summary

Sometimes we have to get creative in order to get the job done, especially when the tools are limited (despite what your "Shop Teacher" may have told you, a "Crescent Wrench" does make a fine hammer!!).  Within SharePoint, imagination and creativity are a must when working with calculated columns, because sometimes, we have to create our own way of doing things.

What we’ve accomplished (through these 3 articles), is a method to validate data that is entered onto a form – a piece of functionality that generally, is only available by use of custom coded solutions (.NET forms, JavaScript, etc.) or InfoPath as the means to enter data into our lists. 

I’ve always thought that not having the ability to use "Regular Expressions" in SharePoint was a major flaw – especially since there are so many forms that get filled out daily (heck, every item on a list is based on filling out a form), but as we’ve seen, there are (in most cases) ways to accommodate what lacks – we just build our own!!  Granted, what we’ve worked through here isn’t necessarily "Regular Expressions" or literally "Find-and-Replace" (those would modify the original data), it’s more of a "lightweight" approach that simply involves string manipulation to get what we want displayed.

As stated in both the previous two articles, I want to emphasize that what we have created is not a complete solution; rather it’s more of an idea on how to approach a given problem.   The problem in our case is how do we reformat data on a list? To really make this a bullet-proof solution, we’d have to take in account for any type of format as well as length of data, which currently we’re not (this is why "Regular Expressions" and coded solutions are nice to use for validating data entry – you can make sure that specific formats and data lengths are adhered to before the data is saved, not after as is in our case).

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.