replace new line character

[ciruliz]

Hi,
I must replace all new line characters in cell contents by something
(perhaps - space)
before converting to CSV, because otherwise CSV becomes unreadable for other
software.

I know, in Ms Word it is possible to replace ^p (which stands for new line)
with something

It does`nt work in Excel.

Any other options how to do that ?

Thanks!
Andis

 

[Chip Pearson]

Select your cells, then display the Replace dialog, With the cursor in the
Find What box, hold down the ALT key and type 0010 on the numeric keypad
(not the numbers above the letters). Nothing will display, but the character
will be there. Then enter something in the Replace With box.

 

[Dave Peterson]

If you used alt-enter for that new line character, you can

Select the range to fix
edit|replace
what: ctrl-j
with: (spacebar) (or whatever you want)
replace all

 

[ciruliz]

good, big thanks for ideas.
Actually I figured out other way :

simple VB macro:

Public Sub ConvertCells()

Dim Cell As Range
For Each Cell In Application.Selection
Cell = Replace(Cell, vbLf, " ")
Next Cell

End Sub


which works as expected

Best regards, Andis