Microsoft has allowed a seamless transition between different programs within the Microsoft suite. For instance, users can copy Excel data and then paste it into SharePoint lists. However, users may sometimes face “The format of the clipboard data is not recognized” when copying and pasting their data.
- One solution is to run a VBA code to clear out any formatting issues that may be preventing your content from being pasted.
- Another solution is clearing your clipboard and copying and pasting the content into a SharePoint list.
Both of the solutions provided above will help to resolve the issue of not being able to paste content in SharePoint due to incorrect formatting. If you have incorrect formatting in Excel or a malfunctioning clipboard, you can use any of the solutions provided above to help resolve the issue.
How to execute a VBA script within an Excel worksheet to remove conditional formatting to fix the formatting problem
The first solution to help resolve the issue of unrecognizable formats when copying content into SharePoint is to use a VBA script to clear the Excel document of any unnecessary formats. Microsoft has provided a VBA script that allows you to remove unnecessary formats that obstruct the process of trying to paste the content into a program such as SharePoint. If you input this VBA into the macro settings, you will then be able to have the ability to copy content without having the error show.
- Firstly, open your Excel document.
- Click on the option for “Developer.”
- Now you need to click on the option for “Visual basic.”
When you click on the Visual Basic option, the VBA panel will open in another desktop panel. While the VBA editor is part of the Excel document, it is opened on a completely different page as you may want to work on the main document and the editor side-by-side.
- Choose the sheet with the problematic content from the “VBA project” section.
On the left-hand side of the Visual Basic editor, there will be a list of different VBA projects. Those are the sheets you can select to proceed with the steps ahead.
- Input the following VBA code.
Sub removebreaks() Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub
- Close the VBA editor.
- Save the document to complete the process.
When you add content to a SharePoint list, you will no longer have the inconsistent formatting error. However, suppose there is essential formatting on your Excel document for a third-party extension. In that case, you must remember that the process will affect the applied third-party extensions.
In that instance, I suggest making a duplicate copy of the file, one with the VBA script and one without. That way, you will have both available additions whenever you require them.
How to clear your desktop clipboard to help resolve the issue of unrecognizable formats in SharePoint
Another solution is to clear your desktop clipboard, which can work if your clipboard is malfunctioning. Please follow the steps below to clear your clipboard effectively.
- Firstly, open the start menu and locate the “Settings” option.
- Now we need to click on the option for “System.”
- Scroll down and click on the option for “Clipboard.”
- Click on “Clear” under “Clear clipboard data.”
- Now go back to your Excel document and copy the content.
- Go into your SharePoint list and paste the content to complete the process.
If the issue persists after using the VBA to clear unnecessary formatting, you may have other bits of selected content in your clipboard preventing you from copying content. If the clipboard has been cleared before you copy and paste the content into a SharePoint list, that will resolve the problem of inconsistent formatting.
Thank you for reading our content on resolving the issue of inconsistent formatting in SharePoint; we have provided a couple of different solutions you can use to help resolve the issue. Both solutions may fix your problem, allowing you to paste the content into SharePoint lists with unrecognized formatting.
You can use the first method to remove any inconsistent formatting from your Excel document so you can paste the content into the SharePoint list. If you cannot paste the content into SharePoint lists despite using the VBA editor steps, you can use the second part of the blog. Here you can clear the clipboard, which could contain unnecessary bits of information that prevent you from copying content into a SharePoint list.