I have a alpha numeric column which has values like "XGS000042210" or "XGS000836485".

How do I extract the number part alone omitting the zeros using calculated column?

For eg: If the column values are "XGS000042210" or "XGS000836485", using calculated column, how can i extract only the values "42210" or "836485"?

2 Answers 2


If you know it is always going to be 3 Letters at the start and the length is always same you can use =INT(RIGHT(Column,9)).

  • Yes, the above formula did the trick, it removed all the zeroes. The output shows the numbers separated by commas. Is there a way I can display the output without commas?
    – George
    Jul 16, 2018 at 15:29
  • =TEXT(INT(RIGHT(Column,9)),0) for reference: support.office.com/en-us/article/…
    – Andrew
    Jul 19, 2018 at 17:00

You can try =TEXT(INT(RIGHT(Column,9)),"0") to convert it to text based.

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.