Calculated Field Error

• Question

•

Hello,

I am getting an error when creating a calculated field. The field is supposed to take a phone number and replace the period's with dash's.

ie.

403.555.1234

to

403-555-1234

The formula is:

=SUBSTITUTE("[Work Phone]", ".", "-")

However I am receiving the following error:

Error

The formula contains a syntax error or is not supported.

Tyler Cranston

Thursday, June 26, 2008 9:56 PM

• I realize this post is a couple months old, but I wanted to help shed some light on this anyway.

In order to get each of the spaces or dots (applies to both poster's having this problem), you have to build out your formula in manner where it iterates through the total number of whatever it is you want to replace.

In the example of the phone number 555.666.7777, there are two dots, so the "REPLACE" function must be called twice through nesting.  Using either "SEARCH" or "FIND", you can find the position of the dot in the string and pass it to the "REPLACE" function so it knows where to replace on.  Once you have this, you take the entire thing and pass it to another "SEARCH" function to find the next occurance of the dot, then pass it's position to another "REPLACE" function where it removes it once again.

In an example of an IP Address, you'd have to run through the "SEARCH" and "REPLACE" functions yet a third time since the address contains three dots.

A formula for this would be:

Notice the first three calls to the "REPLACE" function?  Look at the third one first (innermost calculations occur first - think of standard order-of-operations in math):

The "REPLACE" function takes in 4 parameters: "old_text", "start_num", "num_chars", and "new_text"

So, its formula is:

This replaces (in the value of the "IP Address" field), starting at the position of the first occurance of the "." character, for a length of 1 (replacing 1 character only), with nothing (empty quotes).  This gets rid of the first dot.

To get the rest, we pass this exact same thing back to the next "REPLACE" function where it starts all over again.

So, what it comes down to is that in order to replace all occurances of a given character within a string, we need to know how many times it appears then nest the replacement logic that many times.

Hopefully this clears things up some.

- Dessie

http://www.sharepointblogs.com/dez/default.aspx
http://www.endusersharepoint.com/?cat=397
• Proposed as answer by Wednesday, September 21, 2011 6:12 PM
• Marked as answer by Thursday, January 12, 2012 7:55 AM
Wednesday, November 26, 2008 9:48 PM

All replies

• Hi,

The "SUBSTITUTE" function is not available in SharePoint.

On the Microsoft Office site: http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx

It has full listings of the available functions within calculated columns in SharePoint.

For the "SUBSTITUTE" function, you have to use a combination of the "REPLACE" function with a "FIND" function.

The "FIND" would return the position of the desired character in the string, which would

then pass on to the "REPLACE" function so it can replace it with the new character.

Hope it helps.

Tuesday, July 1, 2008 12:12 PM
• I have used:  =REPLACE(CreateURL,FIND(" ",CreateURL),1,"%20"), but it's only replacing the first space, not all of them.  How can this be applied through all spaces in the URL?
Wednesday, September 24, 2008 4:39 PM
• I realize this post is a couple months old, but I wanted to help shed some light on this anyway.

In order to get each of the spaces or dots (applies to both poster's having this problem), you have to build out your formula in manner where it iterates through the total number of whatever it is you want to replace.

In the example of the phone number 555.666.7777, there are two dots, so the "REPLACE" function must be called twice through nesting.  Using either "SEARCH" or "FIND", you can find the position of the dot in the string and pass it to the "REPLACE" function so it knows where to replace on.  Once you have this, you take the entire thing and pass it to another "SEARCH" function to find the next occurance of the dot, then pass it's position to another "REPLACE" function where it removes it once again.

In an example of an IP Address, you'd have to run through the "SEARCH" and "REPLACE" functions yet a third time since the address contains three dots.

A formula for this would be:

Notice the first three calls to the "REPLACE" function?  Look at the third one first (innermost calculations occur first - think of standard order-of-operations in math):

The "REPLACE" function takes in 4 parameters: "old_text", "start_num", "num_chars", and "new_text"

So, its formula is:

This replaces (in the value of the "IP Address" field), starting at the position of the first occurance of the "." character, for a length of 1 (replacing 1 character only), with nothing (empty quotes).  This gets rid of the first dot.

To get the rest, we pass this exact same thing back to the next "REPLACE" function where it starts all over again.

So, what it comes down to is that in order to replace all occurances of a given character within a string, we need to know how many times it appears then nest the replacement logic that many times.

Hopefully this clears things up some.

- Dessie

http://www.sharepointblogs.com/dez/default.aspx
http://www.endusersharepoint.com/?cat=397
• Proposed as answer by Wednesday, September 21, 2011 6:12 PM
• Marked as answer by Thursday, January 12, 2012 7:55 AM
Wednesday, November 26, 2008 9:48 PM