Sort Sequence Problem

[ajcarr]

I have a range of data in which some items are all numeric and others are mixed character / numeric. I have converted it all to text fields within Excel.

For example Cell 1 = 22640
Cell 2 = 06N0405

If I sort this data ascending, cell 1 comes before cell 2.
However using if(cell 1 > Cell 2) gives the result that cell 1 is larger. Why is this?

 

[mbarron]

Is it consistant through out the column or is it just the top two entries in the column?

I just tested using the two entries you posted and got seemingly bizarre results. I used the sort buttons on the tool bar and it seemed like no sorting was occuring. The problem was because Excel assumed that 22640 was a column header.

Mike

 

[ajcarr]

Mike, it is totally consistent with other data of the same construction. I only included those two examples to show how it worked. It has to be something to do with how Excel decides which string is bigger but I don't know what rules it uses.

What is even more bizarre is that if you assign the cells to string variables in VBA, it performs the checks in the same way that the sort does but if you use the cells themselves it goves the alternate answer!

 

[SkipVought]

aj,

This is a particularly stick problem, because Excel is TOO smart, converting strings that look like numbers to numbers automatically.

1) you must BOTH make the numeric values STRING values by PREFIXING an APOSTROPHY (')

2) you must change for FORMAT of all the cells in the column to TEXT

You are aware that a TEXT sort of NUMERIC CHARACTERS is like this...
[tt]
1
10
2
20
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ajcarr]

Thanks Skip. I thought it must be something like that. The data was converted to text in VBA. I used Application.text rather than Cstr because I have had problems with Excel not converting cells before. However, I didn't expect it to be so persistent in treating text as numeric. I have now assigned all the values to string variables in VBA before doing comparisons - just to make sure.

Allan