StackemEvs;636340 Wrote:
Hi Simon, the character it the "" square.Thats an imported carriage return, try the code below:

Sub test()
Dim r As Range
On Error Resume Next
With CreateObject("VBScript.RegExp")
For Each r In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, 2)
Pattern = "[\n\r]+"
Global = True
r.Value = .Replace(r.Value, " ")
Pattern = "\s{2,}"
Global = True
r.Value = .Replace(r.Value, " ")
Next
End With
End Sub


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176694

Microsoft Office Help