I have a macro that extracts about 4,000 Word comments and puts them into an Excel table. Many of the comments have paragraph marks from Word that I need to get rid of.

When I try to search for these in Excel using Ctrl+J, it works.

But not really:

The data I'm executing the Find/Replace on are all in the same column; the column is set to wrap text. For each cell that contains text that has a paragraph mark, that text is displayed as if there were no line break. For example, here's what a cell looks like when it comes into Excel via a Word comment:
When I initially search a cell for the paragraph mark, Excel does NOT find it.
However, if I double-click into the cell, the line break not only "activates," but remains when I exit the cell, that is, the text is now displayed as two lines, like so:
It is only after I double-click into the cell, then exit, that Excel finds the paragraph mark.


EDIT: This one- to multi-line conversion doesn't occur if I hit escape to exit the cell - it only works if I click on another cell.

How can I search for the Word-sourced paragraph marks without first clicking into thousands of cells? Or, put another way, how can I simultaneously execute the act of double-clicking into thousands of cells so that I can "activate" the paragraph break and thus replace it via Find/Replace?


EDIT 2: I've added a sample file

Attached Files

Sample Data.xlsx (17.2 KB, 8 views)