sort CAS numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I need to sort a column of 6000 items all CAS numbers. I have imported the
column as text from Filemaker so I presume the first step would be converting
text to numbers. The form of the CAs no is: #####-##-# (i.e the last two
coumns separated by dash always contain 2digits and 1 digit respectively. The
first column always vary.
Example:
134-24-9
1455679-00-2
23-12-7
Any suggestion please?
Thank you
 
Hi

You don't indicate which of the numbers you wish to sort
to but assuming it may be the last group, this will work.
Actually it'll work for either of the three groups.

You won't need to convert to numbers unless you desire
to. "Text to Columns" under "Data" in the top tool bar
will do the seperation for you. You'll need three helper
columns to the right of your numbers assuming there is
never more than two "-" in your original numbers.
Highlight your entire column of data. Click through
the "Text to Data" function and select "Other" and insert
a "-" in the box. Click through as directed and make sure
you change the "Destination" to the first helper column
right of your data.

That should give you four columns of data...highlight all
those four columns...select "Data/Sort" and sort to which
ever column you want to sort to.

HTH,

Don
 
Create a single helper column, you might need to sort other
things besides this.

=IF(A1="","", TEXT(LEFT(A1,FIND("-",A1)-1),"00000000")&RIGHT(A1,5))

134-24-9 00000134-24-9
1455679-00-2 01455679-00-2
1455679-33-2 01455679-33-2
2023-12-07 #VALUE!

You want to format your CAS column as text, failure to that might
result in your example becoming a date instead of text and the
VALUE! error.
--
 
Back
Top