Sort Capitalized words ahead of lower case

[Chief_Fred]

I've got a 96000 line spreadsheet with individual words or abbreviations in each cell of the first column. I need to sort it with numbers first, then Capitalized words, then lowercase words. The numbers aren't an issue but sorting the Caps first then lower case without getting words that have a cap in the middle is frying my tiny brain. Originally I dug around here and found a search for caps that returned a True/False then sorted the columns on the result. =MIN(--EXACT(Dict3,LOWER(Dict3)))=0 Then I found that I had abbreviations in the list like cGy and eV or cGMB that incorrectly showed up as true but weren't. Now I need a modification or a better answer. I tried a fw dozen things but obviously I'm barking up the wrong functions.

Help please

Fred

 

[JoeMo]

This returns true or false and should produce TRUE for any text beginning with a capital letter.

=IF(AND(ISERROR(FIND(UPPER(A1),A1,1)),ISERROR(FIND(PROPER(A1),A1,1))),FALSE,TRUE)

 

[Snakehips]

Chief_Fred,

Does this help?

Create a helper column. In this case ColB.
Add formula as below and copy down as required.
Set up a 2 level sort sorting col B first col A second.

Sheet1

*	A	B
****
Fred
Harry
Harry
Name
Tam
Tim
Tom
tam
tim
tiM
tom

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]3[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula B1 =IF(ISNUMBER(A1),1,IF(ISERROR(FIND(UPPER(LEFT(A1,1)),A1,1)),3,2)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Chief_Fred]

JoeMo, Thanks but it doesn't every time for some reason. Cells containing A1c, Aaron's, Aarskog's -it appears anything with an apostrophe shoes false which is moot as they have to be weeded out they told me today anyway - AastromReplicell, AlgiDERM, AllerNaze . . for example show false.

 

[Chief_Fred]

Chief_Fred,

Does this help?

Create a helper column. In this case ColB.
Add formula as below and copy down as required.
Set up a 2 level sort sorting col B first col A second.

Sheet1

*	A	B
****
Fred
Harry
Harry
Name
Tam
Tim
Tom
tam
tim
tiM
tom

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(ISNUMBER(A1),1,IF(ISERROR(FIND(UPPER(LEFT(A1,1)),A1,1)),3,2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

Ype, 99.99999% perfect! The exceptions was the cells containing data like this - 1,2:5,6-dibenzanthracene - which should have been a 1 but returned a 2. Fortunately only about a dozen of those cells exist and excell put them at the top of the "2" list.
Thanks a bunch!

Fred

 

[mikerickson]

How about this
=1+AND(64 < CODE(A1&"x"), CODE(A1&"x") < 91)+2*AND(96 < CODE(A1&"X"), CODE(A1&"X") < 123)

 

[Rick Rothstein]

Ype, 99.99999% perfect! The exceptions was the cells containing data like this - 1,2:5,6-dibenzanthracene - which should have been a 1 but returned a 2. Fortunately only about a dozen of those cells exist and excell put them at the top of the "2" list.

If the leading character can only be a letter or digit (and nothing else), then give this formula a try instead...

=INT(CODE(LEFT(A1))/32.1)

 

[Snakehips]

Chief_Fred,

It would be nice to find that extra 0.00001%.

Rick's formula will let you down if you were to have certain unusual leading characters such as ~
Mike's is fine other than the fact that it will resolve to 1 for a blank cell in A and this will result in any such blank being sorted to the bottom of the 1's.

This can be fixed by modifying as follows..
=1+AND(64 < CODE(A1&"x"), CODE(A1&"x") < 91)+2*AND(96 < CODE(A1&"X"), CODE(A1&"X") < 123)+3*ISBLANK(A1)

so that a blank resolves to 4 and is sorted to the bottom.

 

[Rick Rothstein]

Rick's formula will let you down if you were to have certain unusual leading characters such as ~

Which is why I went out of my way to say...
"If the leading character can only be a letter or digit (and nothing else)..."

Rick's formula will let you down if you were to have certain unusual leading characters such as ~
Mike's is fine other than the fact that it will resolve to 1 for a blank cell in A and this will result in any such blank being sorted to the bottom of the 1's.

This can be fixed by modifying as follows..
=1+AND(64 < CODE(A1&"x"), CODE(A1&"x") < 91)+2*AND(96 < CODE(A1&"X"), CODE(A1&"X") < 123)+3*ISBLANK(A1)

so that a blank resolves to 4 and is sorted to the bottom.

What about the the ~ and other non-letters, non-digits? Fred did not say where such characters should go assuming it were possible for them to be the leading character, but your formula makes them all 1, the same as the digits... won't that make text starting with those characters and text starting with the digits possibly intermingle with each other after being sorted depending on their original order of appearance? So then, I guess we will have to wait for Fred to find out whether non-digits, non-letters are possible starting characters or not and, if so, exactly what he wants done with them. In the meantime, the only place my formula falls down, then, is for blank cells, which is easily fixed using standard means...

=IF(A1="",4,INT(CODE(LEFT(A1))/32.1))

 

[Snakehips]

Rick,

Rick's formula will let you down if you were to have certain unusual leading characters such as ~

The comment was made purely as confirmation of your own statement. It was certainly not intended to be a put down.

I got the impression from Fred's post #5 that some of his leading characters on his exceptions could be - , receding a number. On reflection they are probably just separators in his post and I am just an idiot
I had noticed that your formula would return 1 for a lot of common non-letter, non-digits but not for all, such as ~ which returns 3. Mike's formula,however, returns 1 for all which I thought put them in good shape for the sort.
Perhaps Fred will clarify.