# How to sort cells with text and numbers correctly

This is my example list: example file

As you can see I am sorting all unique elements in row B. But the "Sprint 9" gets sorted as the highest value, not the lowest. I really have no clue how I can fix this. I found this solution and tried to adjust the formula to my needs, but couldn't get it to work as I expect it:

In Google Docs, how to make a column with several numbers in each cell sortable?

Key for the sort is the notation of the digits in the text (String #). These are sorted alphanumerically, meaning that `9` is higher than `25` when sorting descendingly. This can be resolved by squeezing in a zero to all digits ranging from 1 to 9. See formula I constructed.

## Formula

``````=SORT(                  // range
UNIQUE(              // range
ARRAYFORMULA(      // array_formula
IF(
MID(           // logical_expression
A2:A,        // string
9,           // starting_at
1            // extract_length
)="/",
REPLACE(       // value_if_true
A2:A,        // text
8,           // position
0,           // length
"0"          // new_text
),
A2:A           // value_if_false
)
)
),
1,                   // sort_column
TRUE                 // is_ascending
)

copy / paste
=SORT(UNIQUE(ARRAYFORMULA(IF(MID(A2:A, 9, 1)="/",REPLACE(A2:A, 8, 0, "0"), A2:A))), 1, TRUE)
``````

## Screenshot ## Explained

The `MID` formula separates the 9th character from the string placed in `A2:A`. If it matches a `/`, then use the `REPLACE` formula to insert a `0` at position 8, by using zero as start position. If no match has been found, simply show the unaltered range `A2:A`. All is wrapped inside an `ARRAYFORMULA` to take on ranges instead off one cell. The altered range is then fed to the `UNIQUE` formula that will show only unique entries. This range is sorted by the `SORT` formula, using the first column of the range (and the only one) and sorted ascendingly.

I suggest a helper column (ColumnC) populated in C2 with:

``````=value(substitute(mid(A2,find(" ",A2)+1,2),"/",""))
``````

copied down to suit, then sorted by ColumnC not including header.

MID take the first and second characters after the first space (located with FIND) and since one of those might be `/`, SUBSTITUTE replaces any `/` with nothing. VALUE then converts a string to a numeric for number sorting.