How about setting up a separate field for the pre-fixes (the 1,3 or brackets)?

Set up the separate field in the access table as "prefixes", the have the
"main chemical name" in its own field.  This way when you export you will be
able to sort using the the "main chemical name" field.
Good luck.

Hi Anna,

You could create a function that ignores what you want to ignore, then
sort by that.  For example:

Public Function IgnoreNumbersBrackets(InititalValue) As Variant
   Dim intPos As Integer
   Dim strTemp As String
   Let IgnoreNumbersBrackets = Null
   If IsNull(InititalValue) Then Exit Function
   If Len(InititalValue) = 0 Then
       Let IgnoreNumbersBrackets = InititalValue
   End If
   Let strTemp = InititalValue
CheckFirstChar:
   Select Case Left(strTemp, 1)
       Case "0" To "9", "[", "]", " ",","
           Let strTemp = Mid(strTemp, 2)
           GoTo CheckFirstChar
   End Select
   Let IgnoreNumbersBrackets = strTemp
End Function

On May 15, 11:53 am, Anna Busby <Anna B...@discussions.microsoft.com>
wrote:

Add the following function to a standard module:

Public Function ChemName(varChemName As Variant) As String

   Dim i As Integer, n As Integer
   Dim strChemName As String
   
   If Not IsNull(varChemName) Then
       strChemName = varChemName
       n = Len(strChemName)
       ' step through name and remove characters
       ' until first alphabetic character is encountered
       For i = 1 To n
           Select Case Asc(Mid$(strChemName, 1, 1))
               Case 65 To 90    ' upper case letter
               Exit For
               Case 97 To 122    ' lower case letter
               Exit For
               Case Else
               ' remove character
               strChemName = Mid$(strChemName, 2)
           End Select
       Next i
   End If
   
   ChemName = strChemName
       
End Function

You can then call it, passing the chemical names as imported into the
function as its argument, ChemName([Chemical Name])

You can use it in whatever way is most appropriate, e.g.

1.  Permanently remove the leading non-alphabetic characters by means of an
update query:

UPDATE [YourTable]
SET [Chemical Name] = ChemName([Chemical Name]);

2.  View the full name as imported but sort by the name trimmed of the
non-alphabetic characters:

SELECT [Chemical Name]
FROM [YourTable]
ORDER BY ChemName([Chemical Name]);

3.  Keep the full name as imported in the table but view it and sort it
trimmed of the non-alphabetic characters:

SELECT ChemName([Chemical Name]) AS [Trimmed Name]
FROM [YourTable]
ORDER BY ChemName([Chemical Name]);

Ken Sheridan
Stafford, England