Calculated column - extract only the number from alpha numeric string

Question

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

Andrew · Accepted Answer · 2018-07-13 17:08:14Z

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

answered Jul 13, 2018 at 17:08

Andrew

4414 silver badges12 bronze badges

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

Add a comment |

Tecrms · Accepted Answer · 2018-07-16 17:44:51Z

0

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

answered Jul 13, 2018 at 16:30

Tecrms

263 bronze badges

Add a comment |

2 Answers 2