• Status: Solved
• Priority: Medium
• Security: Public
• Views: 490

# String comparison in Crystal Reports and Excel

Hi,
Can anyone please explain to me how Crystal Reports and Excel decide which is the greater of two text strings?

I would have thought that it would work like this:
a. compare character 1 of the two strings
b. If character 1 of both strings is identical, then compare character 2 of each string.
c. Continue until a non-identical pair of characters is found.
d. the string with the higher character is greater than the other one.

Take an example: Compare String 1 "ANTEATERS" to String 2 "AARDVARK"
Compare character 1 of the two strings
Both characters are identical ("A"), so compare character 2 of the two strings
N is greater than A so "ANTEATERS" is greater than "AARDVARK".

But....

In practice it isn't working that way, and I cannot figure out the logic these programs are using.

When I compare "C-" to "CA", Crystal Reports and Excel both tell me that "C-" is less than "CA".
But when I compare "C-W" to "CAB", Crystal Reports and Excel both tell me that "C-W" is greater than "CAB".

Can anyone explain to me how the programs are reaching this conclusion? Any help would be much appreciated!
0
vistamed
1 Solution

Commented:
Hi,

Excel sorts the strings but ignores the hyphen (-) and the apostrophe (') that's why you have this sorting result.

Reference
http://support.microsoft.com/kb/212144/en-us

Regards
0

Author Commented:
Hi Rgonzo1971,

Well I'll be dipped. It appears that Crystal ignores the hyphen in a similar way to Excel. I changed my formulas to replace the hyphens in my strings with a tilde, and sure enough the comparisons gave me the result I was expecting. My next question is why on Earth they arbitrarily decided to ignore hyphens and apostrophes, but that'll have to keep for another day.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.