In hopes that someone can help me:

I am trying to sort in a column the following in "numerical" order:

A2N1, A2N5, A2N10 (In this order)

It keeps sorting as follows: A2N1, A2N10, A2N5

Is there a way to fix this?

many thanks!

The following UDF will extract just the numeric values from your data and
then you can sort the numeric column. Press Alt+F11 to open the VBE, click
Insert on the menu and select MOdule. Paste the following in the blank
module:

Function ExtractNumbers(varVal As Variant) As Long

Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String

intLen = Len(varVal)

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If IsNumeric(strChar) Then strVal = strVal & strChar
Next i

If Len(strVal) = 0 Then
ExtractNumbers = 0
Else
ExtractNumbers = CLng(strVal)
End If

End Function

Insert a blank helper column to the right of the column you wish to sort and
enter the custom formula:

=ExtractNumbers(CellAddress)

Where CellAddress is the first cell containing data. Copy the formula down
the columns and then do a Data/Sort using the helper column. Delete the
helper column following the sort
--
Kevin Backmann


"KAT" wrote:

In hopes that someone can help me:

I am trying to sort in a column the following in "numerical" order:

A2N1, A2N5, A2N10 (In this order)

It keeps sorting as follows: A2N1, A2N10, A2N5

Is there a way to fix this?

many thanks!

Really, nothing is broken.
XL is sorting your data as Text, not as numbers.

If your data is exactly like your example, you can extract the ending
numbers into an adjoining, "helper" column, convert them to XL recognized
numbers, and then sort both columns together, using the "helper" column as
the sort key, and then finally delete that helper column.

With data in Column A, starting in A1, enter this formula into B1:

=--RIGHT(A1,LEN(A1)-3)

Copy down as needed, then sort *both* columns, using B as the key.


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"KAT" wrote in message
...
In hopes that someone can help me:

I am trying to sort in a column the following in "numerical" order:

A2N1, A2N5, A2N10 (In this order)

It keeps sorting as follows: A2N1, A2N10, A2N5

Is there a way to fix this?

many thanks!