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)

Can you add a sample file so we dont have to recreate the data?

I've added a sample file.

The character that is causing the issue is a Carriage return, i think it only shows when you go into the cell and press enter as the cell recalculates at that point



With the quick test that i have done the below code seems to remove them



Sub cr_remove()

Range("a:a").Replace what:=Chr(13), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End Sub

Awesome -- thank you very much for looking at this and for the code!

No problem, make sure it is okay and let me know if you need anything else

Please post in the VBA forum in the future for questions relating to macros