Keep missing-ID markers blank

Turn NULL, \N or N/A into a blank, and keep 000000001 with its zeros.

What you'll make

In Excel: NULL becomes blank; 000000001 becomes 000000001 (zeros kept).

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",…,A8="N/A")Checks for an empty cell, NULL, \N or N/A.No, it has 000000001
2A8&""Keeps the ID as text, zeros included."000000001"
3IF( … )Leaves a marker blank, otherwise uses part 2."000000001"

Read it left to right. Capitals don't matter: null counts too. The IDs here are made-up.

=IF(OR(A8="",A8="NULL",A8="\N",A8="N/A"),"",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 your IDs, stored as text, into the green column of the Template tab. The 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 if your source says NULL, \N and N/A mean missing. A real ID spelled that way would vanish.

Microsoft's help: IF function