I am working on sharepoint 2016 on-premises farm. and i wrote the following power-shell script to update the items of a list:-

>>  $web = get-spweb "http://servername/offices/Departments/IT/"
>>  $list2 = $web.lists["list123"]
>> foreach ($i in $list2.items)
>> {
>> $a = $i["ID"];
>> $sourceItem = $list2.items.GetItemById($a);
>> $sourceItem["NormalNumericColumn"] = $sourceItem["CalcualtedNumericColumn"] ;
>> $sourceItem.Systemupdate();
>> }

where what i am trying to achieve to to assign a normal numeric field, the value of a calculated column (which is also of type number). but the above script will raise the following exception:-

Exception calling "SystemUpdate" with "0" argument(s): "Invalid number value.
A number field contains invalid data. Please check the value and try again."
At line:15 char:1
+ $sourceItem.Systemupdate();
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SPException

now if i try to get the value for a non-calculated column, the script will work fine.. so can anyone adivce if there is a way to the value for a calculated column inside my power-shell script ??

3 Answers 3


Your calculated field, even if defined as a number, isn't a full number - it's structured almost like a lookup - there a float;# in front of it.

You can do a .split(';#')[1] to get the value. It will have decimal values, 2 will show up as 2.0000000000000, so you might have to deal with that..

  • @Robim seems your answer was correct we will get the value as follow "float;#123.00000" so we need to use split
    – John John
    May 17, 2017 at 21:09
  • my calculated field value is "string;#Yes" but I need to use .split(';#')[2] to get the "Yes" value. I will get empty value for [1]. Do you know why?
    – Mark L
    Dec 6, 2019 at 9:18

Try this:

    $cf = [Microsoft.SharePoint.SPFieldCalculated]$list2.Fields["CalcualtedNumericColumn"];
    $cf.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"]) //should output value
  • 1
    try my edit, should run only if $sourceItem is found.
    – Mike
    May 17, 2017 at 20:27
  • 1
    try Write-host $sourceItem["CalcualtedNumericColumn"]
    – Mike
    May 17, 2017 at 20:32
  • 1
    then try $value = [double] $sourceItem["CalcualtedNumericColumn"].toString().Split('#')[1]
    – Mike
    May 17, 2017 at 20:37
  • 1
    not sure, but glad it works, sometimes you have to retype in powershell because the character mapping is different
    – Mike
    May 17, 2017 at 20:59
  • 1
    If you have the value, you won't need that anymore, just assign that value to the other column as you were doing before. $sourceItem["NormalNumericColumn"] = [double] $sourceItem["CalcualtedNumericColumn"].toString().Split('#')‌​[1];
    – Mike
    May 17, 2017 at 21:02

Try the below code, it is basically get the value of Calculated filed as text then convert it to integer value. Once it is Int then you can set it to your Number column.

 $web = get-spweb "http://servername/offices/Departments/IT/"
$list2 = $web.lists["list123"]
  foreach ($i in $list2.items)
$a = $i["ID"]

 $sourceItem = $list2.items.GetItemById($a)

$calculatedfield = $sourceItem.Fields["CalcualtedNumericColumn"] -as [Microsoft.SharePoint.SPFieldCalculated];            
$c = [int]$calculatedfield.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"])
$sourceItem["NormalNumericColumn"] = $c 
  • thanks for the reply..now in my case the column i want to assign the value to and the calculated column are of type number with automatic number of decimals... so i need to use [double] instead of [int] ?is this correct?
    – John John
    May 17, 2017 at 19:44
  • now this $calculatedfield.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"]) will raise the following error "you not call the method on a null-valued expression.."
    – John John
    May 17, 2017 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.