SharePoint calculated column replace all spaces

Question

This is my requirement:

Here, I have taken a single line of text column with 2 spaces in the column field. I have used that column as a reference in a calculated column. By using a formula I can able to replace the first space but I can not able to replace all the remaining spaces. I want every space to fill at the same time. what will happen if I take a column with 4-5 spaces. how to replace all spaces? Is there any way to do it?

Formula:

=IF(ISNUMBER(FIND(" ",[ Employee Contact number])),REPLACE([ Employee Contact number],FIND(" ",[ Employee Contact number]),1," -"),[ Employee Contact number])

Answer 1

Unfortunately, there is no function available in SharePoint calculated column formulas which will replace all spaces from column value automatically.

SharePoint calculated column formulas supports REPLACE function which replaces only one instance of the space character at a time.

So, if you want to replace multiple space characters, you will have to replicate the same REPLACE function logic for all spaces.

You can find some similar questions for more information at:

1. Calculated Column Formula how to replace Substitute
2. Sharepoint function to replace multiple characters
3. replace text with formula in calculate

---

Additional:

If you just want the replaced mobile number for display purpose in SharePoint list view only, you can use JSON formatting for your requirements.

1. Create a calculate column using simple formula like: ="Mobile Number"
2. Then apply JSON column formatting to your calculated column and use below JSON:

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

Where ContactNumber is the internal name of column with spaces in it. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

Follow the steps given here to apply JSON column formatting: Use column formatting to customize SharePoint

Output:

Reference: SharePoint: Replace All Occurrences of Substring in a String using JSON Formatting