Overview

Last time, we began working with the concept of “Data Validation” in a “Contacts” list by creating a calculated column that reformats a phone number from a “space-separated” sequence into a “dash-separated” sequence.

The problem of course, is that as we left it, the only manner in which it would accurately work is if the phone number was entered in using the format of “555 123 4567″ (not really a complete solution).  So, we need to further extend this by adding in the ability to check for more formats.

The next format we’re going to look for is the “Dot-Separated” format (”555.123.4567″).

The “Dot-Separated” format for phone numbers has seen a rise in popularity recently, most likely due to the internet and the format used in domains and subdomains, and although not specifically a standard used throughout the world (most use dashes or spaces), is nonetheless becoming more and more prevalent and can be found on (many) business cards, email signature lines, and publications.

Getting Started

Going back to our Excel spreadsheet (refer to the previous article for its setup if you don’t have it available), we can see the sample phone number we entered and it’s reformatted version including dashes:

The formula we used for this was:
=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))

The above simply breaks the phone number (string) into three parts, uses the “TRIM” function to remove any spaces, and then merges (Concatenates) the three pieces back together with a dash in between each.

In this new format (”555.123.4567″) however, we have to use a different approach since there’s no built-in function to remove all “dots”.  Instead of the “TRIM” function, we’re instead going to use a combination of “REPLACE” and “SEARCH” (sounds suspiciously like the original idea of “find-and-replace” doesn’t it?) to get each occurrence of the “dot” and replace it with a “dash”.

You no doubt may be asking why we didn’t take this approach last time for replacing the spaces with dashes in the “555 666 7777″ phone number format.  While we could have with no problems, the previous formula details how to break a string apart in to multiple pieces (substrings) that can each have a separate process ran on them to come up with the proper formatting, and using the built-in ability of the “TRIM” function seemed most appropriate as the method to take.  The process detailed this time, uses a finite depth (how many times it will run on the same data before ending) that will only allow us to find the first and second occurrences of the “dot”.  Each additional search (i.e. “1.222.333.4444″) will require additional “nestings” of the “Search and Replace” functions that will eventually lead to the maximum number of 8 nesting’s total (Both Excel and SharePoint have this same limitation, although I believe Excel’s limit is actually 7).

In Excel, enter “555.666.7777″ into cell A2 and the following formula into cell C2:
=REPLACE(REPLACE(A2,SEARCH(”.”,A2),1,”-”),SEARCH(”.”,REPLACE(A2,SEARCH(”.”,A2),1,”-”)),1,”-”)

As before, notice how the value displayed in cell C2 is formatted with dash-separators?

Dissecting this formula, we can see the following:
3 “REPLACE” functions
3 “SEARCH” functions

REPLACE(old_text,start_num,num_chars,new_text) – Replaces specific text in a string with the replacement text you want, based on a starting position in the string and specified length of characters you want replaced.

SEARCH(find_text,within_text,start_num) – Used to find the start position of a sequence of characters within a string (not case-sensitive).

Analysis

Breaking out the individual functions in order:

REPLACE(has 4 parts)

1. Old_text = starts “REPLACE” function:
   REPLACE(has 4 parts)
   1. Old_text = A2
   2. Start_num = starts “SEARCH” function
      SEARCH(has 3 parts)
      1. Find_text = “.”
      2. Within_text = A2
      3. Start_num = (presumed to be) 1
   3. Num_chars = 1
   4. New_text = “-”
2. Start_num = starts “SEARCH” function
   SEARCH(has 3 parts)
   1. Find_text = “.”
   2. Within_text = starts “REPLACE” function
      REPLACE(has 4 parts)
      1. Old_text = A2
      2. Start_num = starts “SEARCH” function
         SEARCH(has 3 parts)
         1. Find_text = “.”
         2. Within_text = A2
         3. Start_num = (presumed to be) 1
      3. Num_chars = 1
      4. New_text = “-”
   3. Start_num = (presumed to be) 1
3. Num_chars = 1
4. New_text = “-”

As complicated as this may appear, the logic of what’s taking place can be looked at as:

Complete formula:
=REPLACE(REPLACE(A2,SEARCH(”.”,A2),1,”-”),SEARCH(”.”,REPLACE(A2,SEARCH(”.”,A2),1,”-”)),1,”-”)

Nested functions:
SEARCH(”.”,A2) – rename to SEA1
SEARCH(”.”,A2) – rename to SEA2

Gives us:
=REPLACE(REPLACE(A2,SEA1,1,”-”),SEARCH(”.”,REPLACE(A2,SEA2,1,”-”)),1,”-”)

Taking this a little further:

Nested functions:
REPLACE(A2,SEA2,1,”-”) – rename to REP3
REPLACE(A2,SEA1,1,”-”) – rename to REP2

Gives us:
=REPLACE(REP2,SEARCH(”.”,REP3),1,”-”)

In the 4 parts of the REPLACE function, we have:

1. Old_text = REP2
2. Start_num = SEARCH(”.”,REP3)
3. Num_chars = 1
4. New_text = “-”

Again, it can appear complicated at times when using nested functions, but by breaking them down in a manner such as listed above, it can be easier to see how the logic can flow. 

Another way to look at it is:
=REPLACE(REPLACE(A2,SEARCH(”.”,A2),1,”-”),SEARCH(”.”,REPLACE(A2,SEARCH(”.”,A2),1,”-”)),1,”-”)

We know that the “REPLACE” function has four parts:

1. Old_text
2. Start_num
3. Num_chars
4. New_text

“Old_text” is based on the output of the next “nested” function, which in this case, just happens to be another “REPLACE” function.
“Start_num” is found by performing a “SEARCH” that looks for a dot (.).
“Num_chars” is “1″ because all we want to replace is a single dot.
“New_text” is our dash (-).

So, since the “Old_text” is based on the result of a nested “REPLACE”, we do the same thing again.

In the first nested “REPLACE”, we again have four parts:

1. Old_text
2. Start_num
3. Num_chars
4. New_text

“Old_text” is cell “A2″.
“Start_Num” is based on a “SEARCH” that finds the first dot (.) in cell A2 and returns its position in the string.
“Num_chars” is again 1″ because all we’re looking for is the first occurrence of a dot.
“New_text” is our dash (-).

Running the nested “REPLACE” on the string “555.666.7777″ will return us the string “555-666.7777″ (notice the first dot is replaced with a dash and the second dot is still there?).

This value gets passed back to our original “outer” “REPLACE”, which finds the first occurrence of a dot (in the last part of the string – in between the last “6″ and first “7″) and replaces it with a dash (-).

In order to make sure that our starting position is indeed the dot between the last “6″ and the first “7″, we have to run a nested “REPLACE” again in the “SEARCH” since we’re working with the original data in the cell (we can’t pass the value of the original nested “REPLACE” to our “SEARCH”).

So, basically all we’re doing is running a “REPLACE” multiple times (one for each dot that occurs) in order to switch out each dot character with a dash (clear as mud yet?).

Moving on to SharePoint, we need to now create a new column in our “Employee Contacts” list and add in our formula.

In the “Employee Contacts” list, create a new column called “Home Phone Number 2“, make it a “Calculated” type and paste in our formula from Excel making the following changes:

Formula in Excel:
=REPLACE(REPLACE(A2,SEARCH(”.”,A2),1,”-”),SEARCH(”.”,REPLACE(A2,SEARCH(”.”,A2),1,”-”)),1,”-”)

Replace each occurrence of “A2″ with “[Home Phone]“, making sure that you do use the brackets [] around the name of the column.

Formula in SharePoint:
=REPLACE(REPLACE([Home Phone],SEARCH(”.”,[Home Phone]),1,”-”),SEARCH(”.”,REPLACE([Home Phone],SEARCH(”.”,[Home Phone]),1,”-”)),1,”-”)

Hit “OK” to save the column, and then create a new item on the list:

Note – if you have been following along from the previous post on this topic and still have a test item on the list with the same details as listed below, you can simply modify the “Home Phone” data to use dots instead of spaces in the phone number.  If not, create a new item as listed below.

(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 dots in the number)

Once saved, you see the original “Home Phone” listed with dots and our new column displaying the phone number with dashes (Note – I’ve stripped down the view for readability to only have the contact’s name and phone numbers visible):

As before, all we have to do now is modify the view to use our new column (”Home Phone Number 2″) in the display instead of the default “Home Phone” field (editing the item afterwards is still the same – modify the “Home Phone” field on the item’s details).

We now have a way to reformat phone numbers that are entered in using a “dotted” style into our target format of “dashes”.

 

Summary

This (as mentioned last time and at the beginning of this article) is not a complete solution to the problem since once again this formula will only work on a specific type of entered phone number (numbers entered in using dots as the separators).  So what we really need is a way to capture both spaces and dots, and deal with reformatting both of them at the same time.

In the next post, we’re going to do just that – look for phone numbers entered in with a space-separated style, dot-separated style, and the bonus of checking to see if the phone number was entered in correctly with dashes in the first place, and skip the other two checks altogether if so.

Originally, my plan was to include all of these methods into a single article, but after getting into my first draft and seeing the page count in the high 30’s (and increasing), I decided to break this up into a series of three posts that first tackle each format separately, then (as you’ll see in the next article) bring them altogether in a fashion that uses multiple calculated columns to solve the data validation problem.  Even then, it wont be a complete solution since we’ll only be looking at three (of the numerous) phone number formats, but by the time we’re finished you should be able to see how to extend these ideas further and add in as many other formats as you want.

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