I should have added 1 to the first LEN:

=VALUE(MID(A1,LEN("Sample ")+1,LEN(A1)))

to account for the possible lack of a space between the prefix "Sample" and the number, in case you
really aren't using "sample "

=VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1)))

HTH,
Bernie
MS Excel MVP


"Hreinn" wrote in message
...
How to sort column when cells contain both text and number, that is
Sample 1
Sample 2
Sample 3
etc.

When I sort as usual I get the undesired order
Sample 1
Sample 10
Sample 100
Sample 101
etc.

Instead of
Sample 1
Sample 2
Sample 3
etc.

This is problem is probably related to that Excel takes the cell content
only as text.
Thanks in advance.
--
Hreinn