removing ALT+enter characters from a column

[andy hackett]

I frequently get speadsheets sent to me which have had text entered int
cells that has been separated by the "ALT + Enter" keys to force it ont
a separate line. Does anyone know of a way I can remove thi
automatically and add a comma & space character instead (perhaps with
macro?)

I have attached an example file if you wish to look at it

Thank

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63113

 

[Peo Sjoblom]

One way

Sub CleanCR()
Selection.Replace What:=Chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


Change Replacement="" to " " if you want to replace it with a space

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[andy hackett]

Thanks,

Unfortunately I can't get this to work. I have copied in the code int
vBAeditor and nothing happens when I run it. How do I select the colum
that it needs to work on

 

[andy hackett]

Sorry, Please ignore last post I have now got it working. I wa
selecting an entire column instead of selecting the data in the colum


Thank