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

 

[Rick Rothstein]

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

No problem... I think I reacted off your choice of the words "will let you down".

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.

That is because it was designed solely to work within the range of letters and numbers only (hence the specific lead-in warning).

Perhaps Fred will clarify.

Yes, I am hoping for that too.

 

[Chief_Fred]

OK let me see if I can clarify. The cells should only start with a number or letter but as many of these are plucked from user dictionaries and I've actually found a "?" starting a cell - probably due to converting from Word or Word Perfect to UTF - they don't always. So running each of the suggestions here - and you folks don't know how much I appreciate your efforts, it would have taken me weeks of anger management counseling and much replacement equipment to get anywhere close - here's what I found.
JoMo's solution has trouble with mixed words (caps and lower case) and words with an ' returning "False" (lower case). It also calls a number lower case but calls blanks and ignores the "?" returning True if followed by a cap but "False" if followed by lower case
Snakehips returns a "2" for blanks and the "?"
Mikes original a "1" for both
Mikes modified by SH a "4" for blanks and a "1" for the "?"
Rick's "#Value!" for a blank and a "1" for the "?"
For a cell beginning with a "?" Snakehips returns a "2" and every other solution a "1"
I also tried a "~" and a "`" just to see what would be returned. Here's a look at the results as an unsorted table with SH solution first then JoMo, Mike, Mike's modified by SH and Rick.

zymosterol		3	FALSE	3	3	3
?cGm		2	FALSE	1	1	1
?Cv		2	TRUE	1	1	1
?~Cv		2	TRUE	1	1	1
?-cVg		2	FALSE	1	1	1
~cV		2	FALSE	1	1	3
~Cv		2	TRUE	1	1	3
		2	TRUE	1	4	#VALUE!
`cGm		2	FALSE	1	1	2
`Cgm		2	TRUE	1	1	2
Abiological		2	TRUE	2	2	2
Abbe's		2	FALSE	2	2	2

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I hope that clarifies things. I do have another questions though.
How do I find the words with and apostrophe in them then move them one cell over for A to B? I was going to do it manually but Find tells me there are 3009 in the list. I did a Macro but it didn't work and it screwed the sorts up even though I specified the range as A1:A96389

Once again thanks a bunch. If you get top Texas I'm buying the BBQ Ribs!

Fred

 

[Rick Rothstein]

Okay then, what about this formula...

=IF(ISNUMBER(FIND(UPPER(LEFT(A1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")),INT(CODE(LEFT(A1))/32.1),4)

 

[Chief_Fred]

It still has a #Value! for a blank but works elsewhere. Here's this result on the test field I posted before

zymosterol	3
?cGm	4
?Cv	4
?~Cv	4
?-cVg	4
~cV	4
~Cv	4
	#VALUE!
`cGm	4
`Cgm	4
Abiological	2
Abbe's	2

<colgroup><col><col></colgroup><tbody>
</tbody>

Fred

 

[Rick Rothstein]

It still has a #Value! for a blank but works elsewhere. Here's this result on the test field I posted before

zymosterol	3
?cGm	4
?Cv	4
?~Cv	4
?-cVg	4
~cV	4
~Cv	4
	#VALUE!
`cGm	4
`Cgm	4
Abiological	2
Abbe's	2

<tbody>
</tbody>

Fred

Sorry, I forgot about the blank. Give this a try (you will need to copy/paste it because of the "œ" character...

=IF(ISNUMBER(FIND(UPPER(LEFT(A1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ")),INT(CODE(LEFT(A1&"œ"))/32.1),4)

 

[Snakehips]

Fred,

Try the following to shift text containing apostrophe from Col A to Col B.

Sub MoveApostrophes()
Dim MyRange As Range
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set MyRange = Range("A1:A96389")
For Each Cell In MyRange
If InStr(1, Cell, "'") Then
Cell.Offset(0, 1) = Cell
Cell.Value = ""
End If
Next Cell
ErrHandler:
On Error GoTo 0
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

It might take a minute or so to run but hopefully ok and beats manual.
I have included your stated range as a fixed but you could easily code to give the dynamic end of the range if you wish.

 

[Chief_Fred]

"Sorry, I forgot about the blank. Give this a try (you will need to copy/paste it because of the "œ" character...

=IF(ISNUMBER(FIND(UPPER(LEFT(A1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ")),INT(CODE(LEFT(A1&"œ"))/32.1),4)"

Yep that worked perfectly
Fred

 

[Chief_Fred]

Snakehips said
:It might take a minute or so to run but hopefully ok and beats manual.
I have included your stated range as a fixed but you could easily code to give the dynamic end of the range if you wish."

Worked like a charm. Thank you so much, I would never have worked that out.! I spent 19 years in England mostly around Mildenhall but some in Newbury, if we take that trip back we've been discussing I'll buy a pint or three at your local!
Fred