If the text is indeed identical, I have not heard of any problems with comparison.
How are you comparing the text? With formulas, a macro, or some other method? One problem that comes up for people working with text in Excel, sometimes, is that there are characters that LOOK identical, but are in fact different. Excel supports the full Unicode character set (UTF16), and if text was copied from another source and pasted into Excel, it may include some simple characters that look quite ordinary but aren't, such as non-breaking spaces, various types of dashes, ellipses, minus and addition signs, etc. A few common problem characters can be identified in the ANSI character set (codes 0-255): Code 160 is a nonbreaking space (you may want to replace it with code 32 for a regular space), codes 0-9, 11, 12, 14-31, 127, 129, 141, 143, 144 and 157 are non-printing (invisible) characters. Also, you mention "new line characters" and specify code 10. In windows, line breaks in text ordinarily are specified with both a carraige return and new line (ANSI/ASCII code 13 followed by ANSI/ASCII code 10). Excel is forgiving with this convention, and displays text in cells the same whether you use CR+LF, just CR or just LF. You can check what the character codes in the text are in various ways. Useful worksheet functions are: CODE (supports ANSI codes 0-255) and CHAR (for the inverse). The result is not definitive with those in case there are higher Unicode characters in the text. It can be helpful to use the SUBSTITUTE function to change unwanted characters to appropriate ones (or to strip them entirely). Also useful are CLEAN and TRIM functions. For example, if one piece of text is in A1 and another in B1, you could confirm whether the two cells would match with regular spaces substituted for any non-breaking spaces like so: =SUBSTITUTE(A1,CHAR(160)," ")=SUBSTITUTE(B1,CHAR(160)," ") >From a VBA macro, you can evaluate the actual unicode characters using functions such as CHRW (like CHAR/CHR but Unicode), ASCW (like CODE/ASC but Unicode), MID, INSTR, STRCONV, STRCOMP. You may also find the folling UDF macros from ZVI/Vladimir, and the accompanying discussion (http://www.mrexcel.com/forum/showthread.php?t=422934) helpful: Function Uni2Hex(Txt As String) As String Dim b() As Byte, i&, j& b() = Trim(Txt) j = UBound(b) For i = 0 To j Step 2 If i < j Then Uni2Hex = Uni2Hex & Format(Hex(b(i + 1)), "00") Uni2Hex = Uni2Hex & Format(Hex(b(i)), "00") Next End Function Function Hex2Uni(HexCode As String) As String Dim b() As Byte, i&, j&, s$ s = Replace(HexCode, " ", "") s = Replace(s, "0x", "") j = Len(s) If j <= 4 Then ReDim b(1 To 4) b() = ChrW("&H" & s) Else ReDim b(1 To 8) b() = ChrW("&H" & Mid$(s, 1, j - 4)) & ChrW("&H" & Mid$(s, j - 3)) End If Hex2Uni = b() End Function I *seem* to remember that there is a bug in the above functions (I've noticed this discussion previously) in handling a certain range of Unicode characters. If you or anyone is really interested, I will look into it. Here is a relevant discussion: http://chandoo.org/wp/2012/01/25/cleaning-up-imported-data/ Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Alan Sent: Sunday, April 29, 2012 3:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Problems Comparing New Line Character in Strings I have two sets of Excel worksheets I am trying to compare. The cells contained multiple lines of text. Some identical-looking lines do not compare with string1 = string2 syntax. It appears the comparison is failing when it tries to compare new line characters (ASCII code 10). Has anyone else run into this? I tried to do some Google searches, but no luck in finding answers to this problem. Thanks, Alan -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
