SharePoint Calculated Column Replace All Spaces | SharePoint calculated column add space

In this SharePoint Online tutorial, I will explain how to replace all spaces in a calculated column field of a SharePoint list. And also I will show you how to replace a single space with a comma in a calculated column field of a SharePoint list and how to replace a space in a SharePoint Online list calculated column.

We will see another example of “SharePoint calculated column add space”

Scenario:

I have created a calculated column named Branch ATM Location in a SharePoint Online list. And all the column values that have space should be separated with a comma (,).

For implementing this, I have taken a list column named ‘Branch Location’ with a single line of text containing spaces as a reference to the calculated column.

The below screenshot represents a SharePoint Online list with a calculated column replacing a space:

SharePoint Calculated Column Replace All Spaces

Let us check out an example of “SharePoint Calculated Column Replace All Spaces“.

• Open a SharePoint list, where you want to add a calculated column that replaces a single space.
• Click on +Add column, Select See all column types and Click on Next.

• Enter a name on Create Column page, and select a data type as Calculated (calculation based on other columns).

• Then, I applied the below-given formula in the formula box of a calculated column and Click on Ok.

Replace Space Formula: =IF(ISNUMBER(FIND(" ",Title)),REPLACE(Title,FIND(" ",Title),1,""),Title)
Title - Renamed to User Name.

• After that, it will create a calculated column in a SharePoint list displaying values replacing space as shown below:

This is how to replace a single space in a SharePoint Online list calculated column.

Replace a Single Space in SharePoint Calculated Column with Comma

Now, I will show you how to replace a space with a comma (,) in a calculated column of a SharePoint Online list.

• Open a SharePoint list, click on the + Add column and select See all column types.
• Then, click on the Next button.

• Provide a name on Create Column page, and select a data type as(Calculated).

• Enter the below-given formula in a formula box of a calculated column and Click on Ok.

=IF(ISNUMBER(FIND(" ",[Branch Location])),REPLACE([Branch Location],FIND(" ",[Branch Location]),1,","),[Branch Location])

• Then, it will create a calculated column with spaces separated by commas, as shown below:

This is how to replace a single space replaced by a comma in a SharePoint calculated column.

Replace All Spaces in SharePoint Calculated Column

Now, I will explain how to replace all spaces in a SharePoint Online list calculated column.

Unfortunately, no function in SharePoint calculated column formulas will automatically replace all spaces from column values.

SharePoint calculated column formulas support REPLACE function, which replaces only one instance of the space character at a time. It is possible to replace multiple space characters using JSON formatting to a SharePoint list calculated column.

Scenario:

I have created a calculated column named ‘Replaced Contact Number’ in a SharePoint Online list. And all the column values that have space should be separated with a hyphen (-).

To implement this, I have taken a list column named ‘Branch Contact Number’ as a Number data type containing spaces as a reference to the calculated column.

The below image shows a calculated column that replaces all spaces SharePoint Online list:

• Open a Sharepoint list, click on + Add column, and select See all column types -> Click Next.

• On a create column page, create a calculated column with a simple formula. Enter a name for a calculated column in a Column name section.

• Enter the below simple formula in a formula box and click on Ok.
  • Where Branch Contact Number is a reference column.

="[Branch Contact Number]"

• Then a calculated column is created in a SharePoint list as shown below:

• Expand the drop-down of a calculated column (Replaced Contact Number), Select Column settings and Click on Format this column.

• Click on Advanced mode in the property pane of a Format columns tab.

• In the advanced mode, enter the below given JSON formatting script and Click Save.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=replaceAll([$BranchContactNumber], ' ', '-')",
  "style": {
    "font-weight": "bold"
  }
}

• Now, you can see all the spaces of calculated column values are replaced with hyphens as shown below screenshot:

This is how to replace all spaces of a SharePoint online list calculated column using JSON formatting.

SharePoint calculated column add space

Let us see how to add space in SharePoint calculated column.

For Example, I have a SharePoint Online list with First Name and Last Name columns. Based on that column, I will create a calculated SharePoint column where I want to add space between the first and last names.

Let’s see how to add space in SharePoint calculated column. To implement this, follow the below steps.

• Open a SharePoint Online list and click on the +Add column, then select See all column data type and click Next. Then in this Create column tab, enter the column name and also choose The type of information in this column is (Calculated (calculation based on other columns)) as shown below.

• Then insert the formula by choosing the column from the Insert column and specify The data type returned from this formula is (Single line of text) -> Click on the OK button.

The formula I used to add space in the SharePoint calculated column is given below.

=[Title]&" "&[Last Name]

• Here, you can see that the SharePoint Online calculated column is created where first and last names are separated by space, as shown below.

This is how to add space in SharePoint Online calculated column.

SharePoint calculated column replace character

I will show you how to replace characters in SharePoint calculated column. Then I will explain how to handle errors when a character replaces in SharePoint Online calculated column.

Scenario 1: Here, I have taken a SharePoint Online list containing a SharePoint calculated column where a hyphen separates the name. I have to replace it with the space. To achieve it, I have used REPLACE function.

Scenario 2: I will show you how to handle an error when I insert a formula to replace a character in SharePoint calculated column, but the character I want to replace is already in that form so, it gives an error. I will implement the IF condition and ISERROR function with REPLACE function.

Replace a character in SharePoint calculated column

Let’s see how to replace a character in SharePoint Online calculated column. To achieve it, follow the below steps.

• Open a SharePoint Online list and then click on + Add column -> Click on See all column types then click on Next.
• Inside the Create column, enter the column name then select Calculated (calculation based on other columns) as shown below.

• Then, insert the formula as mentioned below in the Formula box and then select The data type returned from this formula is -> Click on OK.

Syntax to replace character in SharePoint calculated column using REPLACE function.

=REPLACE([Column 1],FIND("-",[Column 1]),1," ")

The formula used to replace characters in SharePoint calculated column is listed below. I have taken the Title column in place of Column 1 using REPLACE function.

=REPLACE(Title,FIND("-",Title),1," ")

• Now you can see the hyphen in between the name is replaced by the space as shown below.

This is how to replace the character in SharePoint Online calculated column.

Handle #VALUE! ERROR when a character replaces in SharePoint calculated column

Let’s find out how to handle an error when a character replaces in SharePoint Online calculated column. To achieve it follow the below steps.

• Go to the Edit column using the steps of the previous section then insert the formula using the IF condition and ISERROR function with REPLACE function.

The syntax for handling an error in the SharePoint calculated column using the ISERROR and REPLACE functions is given below.

=IF(ISERROR(REPLACE([Column 1],FIND("-",[Column 1]),1," ")),[Column 1],REPLACE([Column 1],FIND("-",[Column 1]),1," "))

The formula I have used to handle an error in SharePoint calculated column by replacing Column 1 with the Title column is listed below:

=IF(ISERROR(REPLACE(Title,FIND("-",Title),1," ")),Title,REPLACE(Title,FIND("-",Title),1," "))

• Then select The data type returned from this formula is and click on OK. Now you can see the error is resolved as shown below.

This is how to handle an error when a character replaces in SharePoint Online calculated column.

Conclusion

Through this SharePoint Online tutorial, I have shown you how to replace a single space with a comma in a calculated column of a SharePoint list. Then, I explained a SharePoint Online list calculated column replace a single space. And also, I have explained how to replace all spaces of a SharePoint Online list calculated column values using JSON statement.

You may also like:

• SharePoint Online Calculated Column AND Condition
• SharePoint Calculated Column Concatenate Function
• SharePoint Calculated Column Convert Text to Number