How does excel analyse text?

[theta]

Hi. How does excel analyse text in formulas. More specifically

="A"<"B" = TRUE
="B"<"A" = FALSE

So are they given a numeric value of some sort?

And could I use this with MIN or SMALL when sorting numerics from text? SUMPRODUCT + COUTNIF?

Thanks

 

[theta]

Trying to use the COUNTIF(A1:A10,"<="&A1) to sort and filter a list of alpha and numeric items

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

1 seems to be treated the same as "A" which does not help

Don't really want a helper column so an ARRAY formula using ROW() for the index, or SUMPRODUCT would be great.

This would help alot with all of my work

Effectively building an advanced sort unique, but as a formula

 

[GlennUK]

What do you mean

1 seems to be treated the same as "A" which does not help

... what makes you say that?

 

[theta]

If I create a list of

A
B
C
1
E
F
G
2

Then the number applied (when the formula provided in column B) is copied down, the same 'index' is given to 1 as to 'A'

This would be a great formula if I could get it to work.

There are formulas for sorting text, or sorting numbers...but not seen on for both ?

 

[cornflakegirl]

theta - I don't think that's anything to do with A being equated with 1. I think that COUNTIF is ignoring the numbers when evaluating the formula for the text entries and vice versa. Otherwise, for B you'd expect the answer to be four, as 1, A, 2 and B should be less than or equal to B.

Don't know why COUNTIF works like that though, give that a boolean test shows that numbers are less than text values (per sort order).

 

[theta]

Some way of excel interpreting text e.g.

=CODE("a") gives 97
=CODE("b") gives 98

?

 

[GlennUK]

Can you describe exactly what you want to do? Sometimes it's easier to give solutions if you know the whole problem and not just a small part of it.

 

[theta]

Want to be able to build a unique sort formula for text and numbers :
1
A
C
2
Z
C
C

And a seond unique formula (the most important one)

1
2
A
C
Z

SUMPRODUCT is favoured for anything array based (used with SMALL and COUNTIF to exclude duplicates?)