Remove one credential from a list

Take LPN out of BSN, RN, LPN and keep the rest in order.

What you'll make

In Excel: BSN, RN, LPN minus LPN becomes BSN, RN.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TRIM(TEXTSPLIT(A8,,","))Splits the list at commas. LET calls it t."BSN" "RN" "LPN"
2UPPER(t)<>UPPER(B8)Checks each one isn't the one to remove.Yes, yes, no
3FILTER(t, … ,"")Keeps the ones part 2 says yes to."BSN" "RN"
4TEXTJOIN(", ",TRUE, … )Joins them back with commas."BSN, RN"
5LET( … )Lets the formula use part 1 twice."BSN, RN"

Each bracket wraps the one above it, so Excel works from the inside out. Capitals don't matter.

=LET(t,TRIM(TEXTSPLIT(A8,,",")),TEXTJOIN(", ",TRUE,FILTER(t,UPPER(t)<>UPPER(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 lists. Paste credential lists into the first green column and the one to remove next to it. The remaining credentials fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only whole credentials that match are removed, every time they appear. Remove one only when you know it no longer applies.

Microsoft's help: FILTER function