Sort data order

[amna77]

Hi,
Is there any way that I can sort the data. In my column A I have numbers, like 1,2,24B, 13A, 11B. its mix of numbers and letters.

Is there any way that you can sort this column A.If I sort the data like that, then it does not work.
Any macro for that?

Thanks in Advance.

[Mark W.]

Explain why "13A" should be sorted before "11B".

[amna77]

No I don't care about letters order.
I just want numbers to be in order, like I, 2, 3, 13. I don't care what letter they have. My column is mix of numbers and letters. I want to sort that column by numbers only.

[Yogi Anand]

On 2002-05-17 05:37, amna77 wrote:
Hi,
Is there any way that I can sort the data. In my column A I have numbers, like 1,2,24B, 13A, 11B. its mix of numbers and letters.

Is there any way that you can sort this column A.If I sort the data like that, then it does not work.
Any macro for that?

Thanks in Advance.

If your data set is in cells A1:A7, then the following array formula will sort the numeric portion of the cell values in ascending order:

=SMALL(IF(ISTEXT(A1:A7),LEFT(A1:A7,LEN(A1:A7)-1)+0,A1:A7),ROW(A1:A7))

Regards!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 16:28 ]

[Von Pookie]

Or, just format the selected cells to "text" and then sort.

[Mark W.]

On 2002-05-17 09:01, Von Pookie wrote:
Or, just format the selected cells to "text" and then sort.

The formatting step alone doesn't accomplish anything since formatting won't covert the numeric values to text. Even if formatting would accomplish this conversion the results would not be as desired...

{"1";"11B";"12A";"2";"24B"}

While sorting with the existing mix of numeric and text data types would produce the desired results for these values...

{1,2,"11B","12A","24B"}

...it wouldn't work for...

{1,2,"1B","12A","24B"}

[ This Message was edited by: Mark W. on 2002-05-17 09:27 ]