Build a provider-site key

Join provider P01234 and site S007 into P01234|S007, only when both are there.

What you'll make

In Excel: P01234 + S007 becomes P01234|S007.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",B8="")Checks if either ID is empty.No, both are filled
2"Review missing key"Shown when an ID is missing.Not used for row 8
3A8&"|"&B8The provider ID, a | sign, then the site ID."P01234|S007"
4IF( … )Uses part 2 if part 1 finds a gap, else part 3."P01234|S007"

Read it left to right. The | sign keeps the two IDs apart. The IDs here are made-up.

=IF(OR(A8="",B8=""),"Review missing key",A8&"|"&B8)

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

Before you send

Make sure no ID already contains a | sign. The key uses it to keep the two IDs apart.

Microsoft's help: IF function