Trim for Hard Returns

[Andrew]

I have two columns (A & B), and I want to compare the data in values in
corresponding cells. Unfortunately, the values in cell A3 (for example) may
have a hard return in it, but the value in cell B3 does not. I know that the
trim function will remove spaces, but is there a way to remove hard returns?
Does this make sense?

 

[Gary''s Student]

Select the cells you want to "trim" and run:


Sub trim_hard_returns()
Dim s As String
Dim r As Range
s = Chr(10)
For Each r In Selection
r.Value = Application.Substitute(r.Value, s, "")
Next
End Sub

 

[Bob Phillips]

Use the Replace method

Replace(Range("A1").Value,CHR(13),"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Andrew]

Thank you both for the suggestions. Unfortunately, neither will work.
Gary's Student's suggestion involved macros, which I'd like to avoid using
for this project, and Bob Phillips's suggestion is giving me an error. Here
is what I've got:

A1:
Testing to see
how this works,
but it doesn't.

B1:
Testing to see how this works, but it doesn't.

C1:
=IF(TRIM(A1)=TRIM(B1),"","Wrong")

The "wrong" is returned in instances where A1 and B1 are not the same
(ignoring spaces) But, in this case, since there are hard returns as well,
it doesn't work. I tried using a REPLACE function for the enter key...

=REPLACE(A1,FIND(CHAR(10),A1,1),1,"")

....but that one only removed the first hard return. The other hard returns
are just showing as squares now. Any more ideas what function I could put in
there to make it work?

 

[Andrew]

After browsing around, I think I may have posted this in the wrong forum. It
probably should've gone in the "Excel Worksheet Functions" forum, so I
apologize. Should I re-post this there?

 

[Bob Phillips]

No, your here now.

Mine was also VBA BTW.

Try this

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

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Andrew]

Thanks, Bob! You saved me SO MUCH TIME!!! It works like a charm.

 

[Ken McLennan]

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.