G'day there Andrew,

=SUBSTITUTE(A1,CHAR(13),"")

and play with CHAR(10), CHAR(160) if 13 doesn't work.

I realise that you said you preferred not to use VBA, but just in
case you change your mind (or in case it's useful to someone else) here
is a smallish function I wrote when trying to parse a random string:


Public Function stripGuff(strCELLCONTENTS As String)
' Remove codes that play havoc with string functions
strCELLCONTENTS = Replace(strCELLCONTENTS, Chr(160), " ")
strCELLCONTENTS = Replace(strCELLCONTENTS, Chr(10), " ")
strCELLCONTENTS = Replace(strCELLCONTENTS, Chr(13), " ")

' Not interested in OC marker here
strCELLCONTENTS = Replace(strCELLCONTENTS, "*", "")

(The above removes asterisks, but you can change it to suit)

' Remove multiple contiguous spaces
Do While InStr(1, strCELLCONTENTS, " ") 0
strCELLCONTENTS = Replace(strCELLCONTENTS, " ", " ")
Loop

(The following were for my specific situation, but once again you can
alter to suit or simply delete them)

' Remove spaces adjacent to hyphen
strCELLCONTENTS = Replace(strCELLCONTENTS, " - ", "-")
strCELLCONTENTS = Replace(strCELLCONTENTS, "- ", "-")
strCELLCONTENTS = Replace(strCELLCONTENTS, " -", "-")
strCELLCONTENTS = Replace(strCELLCONTENTS, "mn", "a")
strCELLCONTENTS = Replace(strCELLCONTENTS, "md", "p")
strCELLCONTENTS = Replace(strCELLCONTENTS, "m", "")
stripGuff = strCELLCONTENTS
End Function

I hope it's useful to someone. I've had enormous assistance from
this group so I'd like to think I can ease at least one problem for
someone else.

--
See ya,
Ken McLennan
Qld, Australia