Close but no cigar. If the first dash "-" was always the decider, something
like the following would work:

SELECT TblSortByText.Chemical
FROM TblSortByText
ORDER BY Mid([Chemical],InStr([Chemical],"-")+1);

However you want to bis removed from consideration to. I'll have to think
about this a little longer.

Are you saying you want to sort based on the first upper case letter
followed by the rest of the characters?
Or is there a different meaning to how you want to sort.

I would think this would require a custom function to build the sort word
and then sort by that
UNTESTED VBA FUNCTION FOLLOWS

In the query add a field
sBuildSortWord ([Your Field]) and sort by this field.

Function sBuildSortWord(strIn)
Dim i as Long, iAsc as Long
Dim strReturn as string

 If Len(strIn & vbNullString) = 0 Then
   sBuildSortWord = strIn
 Else
'Identify first upper case letter and build sort word
    For i = 1 to Len(strIn)
       iAsc= Asc(Mid(strIn,i))
       If iAsc >= 65 and iAsc<=90 then
          ' Truncate at first ucase letter (and add word at end to handle
ties)
          strReturn= Mid(strIN,i) & strIn
          Exit For
      End if
   Next i

'No upper case letter so sort word should be at end of sort.
   If strReturn = vbNullString then
       strReturn = "zzzzzzzzzzz" & strIn
  End if
  sBuildSortWord = strReturn

End Function