0

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.

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

1 Answer 1

0

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

=TEXT(VALUE("000000123456"),"0")

=TEXT(VALUE("00000012345678"),"0")

returns the value as text (without thousands separator)

=VALUE("C00012345")

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") )
2
  • 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
    Commented Mar 27, 2017 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 Commented Mar 28, 2017 at 6:36

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.