Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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 -- Gary's Student "Andrew" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
Use the Replace method
Replace(Range("A1").Value,CHR(13),"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Andrew" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
Thanks, Bob! You saved me SO MUCH TIME!!! It works like a charm.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim for Hard Returns
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing hard returns in long text strings. | Excel Discussion (Misc queries) | |||
Removing hard returns | Excel Discussion (Misc queries) | |||
hard returns | Excel Discussion (Misc queries) | |||
Hard returns in a formula? | Excel Worksheet Functions | |||
how to remove hard returns, which show as little boxes | Excel Discussion (Misc queries) |