0

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"?

3

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)).

2
  • 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 '18 at 15:29
  • =TEXT(INT(RIGHT(Column,9)),0) for reference: support.office.com/en-us/article/…
    – Andrew
    Jul 19 '18 at 17:00
0

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, privacy policy and cookie policy

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