Read the numeric sequence from a code

Turn the code AB000123 into the number 123, with the front zeros dropped.

What you'll make

In Excel: AB000123 becomes 123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The code: two letters, then six digits."AB000123"
2RIGHT( … ,6)Keeps the last six characters."000123"
3VALUE( … )Reads them as a number, so the zeros drop.123

Read it from the inside out. The codes here are made-up.

=VALUE(RIGHT(A8,6))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own codes. Paste codes like AB000123 into the green column of the Template tab. The numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This gives only the number, not the full ID. Keep the original code for anything you send.

Microsoft's help: VALUE function