SharePoint Lookup Column - Number Format to remove commas

0
votes

I have a lookup column in SharePoint 2019 that is looking back to a number field from a custom list.

I used the following JSON to format the lookup column to display the number without the hyperlink:

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField.lookupValue"
}

I want to also remove the thousands commas to display the numbers from 1,234 to just 1234

Any suggestions on how this can be done simply? Seems odd there is no simple option to change the view format. (P.S. my JSON coding ability isn't great!)

3
jsonsharepointformatlookup

3 Answers

1
votes

There are a couple of ways to achieve this, some are a bit hack-ish but work: if you are using Modern List Experience you can simply achieve this by applying following conditional formatting JSON to the number column in your lookup list:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField > 0,'', '')"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

For Classic Experience a workaround could be to keep the data type of your number column as single line of text in your lookup list and apply the following formula in column validation section:

=ISNUMBER([text-column-name]+0)

hope this helps.

0
votes

You could try the below json code:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": {
        "operator": "+",
        "operands": [
            "=substring(@currentField.lookupValue,0,1)",           
            "=substring(@currentField.lookupValue,2,6)"
        ]   
    }
}
-1
votes

Change txtContent to =replace(@currentField.lookupValue, ',', ''):

{      
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", 
  "elmType": "div",
  "txtContent": "=replace(@currentField.lookupValue, ',', '')"   
}