Solved

# String comparison in Crystal Reports and Excel

Posted on 2014-04-02
413 Views
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
Question by:vistamed

LVL 48

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 39971959
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 Comment

ID: 39972070
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

## Featured Post

### Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.