To remove line breaks from your file, open the offending metadata file in Excel, then follow these steps: Do a Search or Find of the document, and select Replace. Holding the ALT key, enter 010 into the Find what field. The above test was with a non-admin user on an existing excel file. Then created a new file, set the owner, but still, a test account could reset the ownership simply by opening and saving in excel. Any ideas about how to prevent this? Yes, NTFS Ownership. Don't care about document the metadata at this point. The idea is to create a new permission level without “Client integration features” and assign it to users to disable export to excel. To create a new permission level, follow these steps: Click on Settings Gear Choose the “Site Permissions” link. “Advanced permissions settings.” Click on “Permission Levels” from the ribbon. Re: Dynamically update metadata from a cell. I had a similar problem so I used VBA to run and save on closing the file. 'Sub AutoClose ' Remove the quotes and replace the names in below. ActiveWorkbook.ContentTypeProperties ('YourMetadataName').Value = Range ('YourCellValueInExcelFile').Value. Above will update the Metadata. This method strips metadata from several files at the same time. Open Windows Explorer and go to the file with the metadata you want to take out. Right-click it to select Properties. Under Properties, click the Details tab then select both Remove Properties and Personal Information.

Recently, I’ve been given a task to remove personal info via scripting, for a set of documents, which consisted of Word, Excel, and PowerPoint documents, both old, and new format (doc, docx, xls, xlsx, etc.)

Testing revealed, that RemoveDocumentInformation has a lot of limitations.

It the below script, I tried to catch most of the errors, but still, exceptions occur.

I hope it will help someone, who is tasked to do such thing.

Script requires Office applications to be available on the host.

It will ask for source directory, it is also used for storing the log files.
After script completion, you will see summary screen, showing total processed documents and log file locations. All documents that script is unable to process for some reason, goes to error log.

Remove Metadata From Excel 2016 Online

Script removes all modification protection and personal information from documetnts in source directory, except documents with password to open (encrypted), and excel files with worksheets locked for editing.
“password to modify” and “suggested read only” are removed from all documents along with personal information.

The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces.

1. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces.

Note: the TRIM function does not remove single spaces between words.

2. To get the length of a string, use the LEN function.

Explanation: the LEN function counts 2 spaces, 2 characters, 4 spaces, 3 characters and 2 spaces.

3. You can use the SUBSTITUTE function to remove all spaces.

Note: the SUBSTITUTE function substitutes spaces (second argument) with empty strings (third argument).

Text imported from other applications may contain ASCII characters. The CLEAN function removes the first 32 non-printable ASCII characters (codes 0 through 31).

4. The CLEAN function below removes a non-printable character, CHAR(7).

5. Simply combine CLEAN and TRIM to remove non-printable characters and spaces.

6. The CLEAN function removes line breaks. To enter a line break, press ALT + ENTER.

7. You can also use the SUBSTITUTE function to remove a line break, CHAR(10), and replace it with something else. For example, a comma and a space.

The CLEAN function doesn't remove all ASCII characters. Simply use the SUBSTITUTE function to remove other ASCII characters.

8. First, find out the code number of the character.

9. Next, use SUBSTITUTE and CHAR to return the text string without this character.

Remove Metadata From Excel 2016

10. If your text contains non-breaking spaces, CHAR(160), the TRIM function doesn't work.

Remove Metadata From Excel 2016 Download

11. You can use the SUBSTITUTE function to substitute non-breaking spaces, CHAR(160) with normal spaces, CHAR(32). Don't forget to add a TRIM function to remove the extra spaces.