Decode plus-sign spaces in form text

Turn + and %20 into spaces, then %2B back into a plus sign.

What you'll make

In Excel: Ava+Reed+%2B+Noah+Park becomes Ava Reed + Noah Park.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1SUBSTITUTE(A8,"+"," ")Turns each + sign into a space."Ava Reed %2B Noah…"
2SUBSTITUTE( … "%20" … )Turns each %20 into a space."Ava Reed %2B Noah…"
3SUBSTITUTE( … "%2B" … )Turns each %2B back into a + sign."Ava Reed + Noah Park"
4SUBSTITUTE( … "%2b" … )Does the same for a lowercase %2b."Ava Reed + Noah Park"

Each bracket wraps the one above it, so Excel works from the inside out. + goes first, so a decoded %2B stays a plus.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"+"," "),"%20"," "),"%2B","+"),"%2b","+")

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 fields. Paste one field per row into the green column of the Template tab. The decoded text fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only on form text where a + stands for a space. Other codes, like %40, stay as they are.

Microsoft's help: SUBSTITUTE function