Windows – Copying a multiline text into a single cell in Excel

It is actually expected behavior (well expected by Microsoft, not necessarily by users) which is the good news. And there is a quite simple work around which is the even better news.

When you paste in a piece of formatted text the cell gets given the "Normal" style and then has the required formatting layered on top. The "Normal" style by default has protection set to locked. So this is why it is being applied.

The solution is to modify the "Normal" style. To replicate a solved version of this problem create a new workbook, then:

1) On the Home Ribbon in the Styles section right click on the Normal box and click Modify. Press the Format button and on the protection tab uncheck Locked. (Remember this only applies to this spreadsheet, but that all cells will now be unprotected rather than protected).

2) Select All cells, right click and choose format cells, and under protection check Locked.

3) Select A1 only, right click, choose format cells, and under protection uncheck locked.

4) Protect the worksheet

Now we should be back at the same point as the example in the question in terms of locked and unlocked cells but with a different Normal style.

Now when I paste text in from word or elsewhere the cell does not become protected.

Ctrl+C > Ctrl+V > Backspace is probably better than the accepted answer due to fewer key strokes. I know this doesn't really answer the question, it sounds like your question is really a feature request. As mentioned in the comments here is a macro solution as well:

https://stackoverflow.com/q/14695836/145173

Sounds like too much work to me to avoid a backspace key stroke. If it's large grabs and pulls, I would just suggest using Notepad++'s advanced find/replace and exploit the \n\r\t characters that are the real text appended to the copy/paste and replace them with nothing.