Remove a trailing .0 from an ID

Turn 012345678.0 back into 012345678. Leading zeros stay.

What you'll make

In Excel: 012345678.0 becomes 012345678.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1RIGHT(A8,2)=".0"Checks whether the ID ends in .0.TRUE
2LEFT(A8,LEN(A8)-2)Keeps all but the last two characters."012345678"
3A8Otherwise keeps the ID as it is.Not used for row 8
4IF( … )Takes off .0 only when it is at the very end."012345678"

Only one final .0 comes off. An ID ending in .00 stays as it is. The IDs here are made-up.

=IF(RIGHT(A8,2)=".0",LEFT(A8,LEN(A8)-2),A8)

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 IDs. Paste IDs into the green column of the Template tab. Clean IDs fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only where you know the .0 was added by mistake, not where it belongs to the ID.

Microsoft's help: RIGHT function