Sort by number only in cell

[serendipity258]

I have files with a bunch of cells with mixed number/text information. I
would like to sort by the number only.

Each cell contains a 1-3 digit number, and 0-2 letters in some combination
of prefix and suffix.

For example, a list might be:
3
A100C
A100D
AB205
B4
53DE

And I would like it sorted by the number only. Actually, I would like it
sorted by the number, then the suffix, then the prefix, but the number is a
big step in that direction...

Thank you

 

[Ron Rosenfeld]

I have files with a bunch of cells with mixed number/text information. I
would like to sort by the number only.

Each cell contains a 1-3 digit number, and 0-2 letters in some combination
of prefix and suffix.

For example, a list might be:
3
A100C
A100D
AB205
B4
53DE

And I would like it sorted by the number only. Actually, I would like it
sorted by the number, then the suffix, then the prefix, but the number is a
big step in that direction...

Thank you

Set up three blank columns to one side of your data.

Label your columns:
Data Number Prefix Suffix

Assuming your Data starts in A2, enter the following formulas to extract the
parts of the data:

Number:
=LOOKUP(9.9E+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1,2,3,4,5,6,7,8,9,0")),ROW(INDIRECT("1:"&LEN(A2)))))

Prefix:
=LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1,2,3,4,5,6,7,8,9,0"))-1)

Suffix:
=MID(A2,LEN(B2)+LEN(C2)+1,255)

Then select the formulas and fill down as far as your data extends.

Finally, with a selection in the table range, or after selecting the table:

Data/Sort
First by Number
Then by Suffix
Then by Prefix
--ron

 

[Liliana]

Microsoft has a ready-rolled array formula:

http://office.microsoft.com/en-gb/excel/HA011549011033.aspx

Don't forget Ctrl-Shift-Enter