Group a 16-character ID in blocks of four

Turn 1234567890123456 into 1234 5678 9012 3456, for IDs stored as text.

What you'll make

In Excel: 1234567890123456 becomes 1234 5678 9012 3456.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(A8,4)Takes the first four characters."1234"
2MID(A8,5,4)Takes characters 5 to 8."1234 5678"
3MID(A8,9,4)Takes characters 9 to 12."1234 5678 9012"
4RIGHT(A8,4)Takes the last four characters."1234 5678 9012 3456"

Read it left to right. Each &" "& adds a space between blocks. The IDs here are made-up.

=LEFT(A8,4)&" "&MID(A8,5,4)&" "&MID(A8,9,4)&" "&RIGHT(A8,4)

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 sixteen-character IDs, stored as text, into the green column of the Template tab. The grouped IDs fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Paste IDs that were stored as text. A long number Excel already changed can't be fixed by this.

Microsoft's help: MID function