Forum Discussion
Remove Pilcrows in Word
Select the table in Word.
Press Ctrl+H to activate the Replace dialog.
Enter ^p in the 'Find what' box. ^p is the code for a paragraph mark (displayed as a pilcrow in Word).
In the 'Replace with' box, enter something that doesn't otherwise occur in the table, for example @@@.
Click 'Replace All'.
Now copy and paste the table into Excel.
With the pasted table selected, ress Ctrl+H to activate the Replace dialog.
Enter @@@ in the 'Find what' box.
In the 'Replace with' box, press Ctrl+J. This will enter an invisible line break.
Click 'Replace All'.
Make sure that the 'Wrap Text' button on the Home tab of the ribbon is on (highlighted).
E.g. in the above example:-
M
6 miles
is now:-
M
6 miles
which throws out my formulae that use LEFT, RIGHT and MID to extract data.
How can I stop these additional "line feeds"
Are you sure that you used the same text string in Word and in Excel?
If not, that might explain what you describe. For example, if you replace ^p with @@@ in Word, but then replace @ with Ctrl+J in Excel, each paragraph break would be replaced with 3 line breaks.
- Thank you for your answer. Sorry I did not come back ealier but am under some other pressures so cannot allow all time I should for this application.