I have a SharePoint List in which one column has Account numbers like 000000123456 000012345678 C00012345

Now I have to create a calculated Column which can display the above fields as 123456 12345678 C00012345.

  • So, are you just looking to truncate the first several digits? Or what? Mar 27 '17 at 19:13

See: http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List



returns the value as text (without thousands separator)


is an error, because its not a value

So the complete formula with a basefield MyValue:

=IF( ISERROR(VALUE(MyValue))  ,  MyValue  ,  TEXT(VALUE(MyValue),"0") )
  • I appreciate your help. I am new to this stuff. Can you elaborate on MyValue. =TEXT(VALUE([Account Number]),"0") removes the zero's from Account Number column. Do I need to save it as one column (let's say Account Number 1, and then create another Calculated column with =IF( ISERROR(VALUE([Account Number 1])) , [Account Number 1] , TEXT(VALUE([Account Number 1]),"0") )?? Please give the formula exactly.
    – Kaushik
    Mar 27 '17 at 20:35
  • Yes, as by your example they remove the leading zeros. The Formula is exact as you replicate with you own Field. Start learning Formulas with Excel, much easier/faster Mar 28 '17 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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