Turn _x0020_ into spaces in headers

Turn First_x005F_x0020_Name from a SharePoint export back into First Name.

What you'll make

In Excel: First_x0020_Name becomes First Name.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The column name from the export."First_x005F_x0020_Name"
2"_x005F_x0020_"The code the export uses for a space."_x005F_x0020_"
3" "What goes in its place: one space." "
4SUBSTITUTE( … )Turns every _x005F_x0020_ into a space."First Name"

Only _x005F_x0020_ changes. Other codes, and capitals like _X0020_, stay as they are.

=SUBSTITUTE(A8,"_x005F_x0020_"," ")

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 column names. Paste the exported column names into the green column of the Template tab. The names with spaces fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Some column names, like Author, differ from the title people see. Check the list's own display names.

Microsoft's help: SUBSTITUTE function