CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

Alphanumeric sorting in Excel?

Are there advanced sorting settings in excel that takes a string of data like "P9, P50, P8, P100," and arrange them in alphanumeric order? Doing it normally puts the order P100, P50, P8, P9, as it doesn't register P100 as being larger than P9?

Upvotes: 0

Views: 466

Answers (2)

FerPeralta
FerPeralta

Reputation: 146

You can add a auxiliary column with only the numeric data from the alphanumeric column (using excel functions like MID(text, start_num, num_chars)), and then sort the rows using this new column (yoy can hide this column).

Excel defaults options don't allow this kind of sorting, i'm afraid.

Upvotes: 2

Balinti
Balinti

Reputation: 1534

You can parse the data based on the comma and whatever you desire with text to columns. Let’s say, if your data contains always P and the number at the beginning choose the Data ribbon-> text to column -> delimited -> other and type there:

 , P

That will extract all of the rows… (For the 1st P just add , with & command (concatenate) ) And then you will have only numbers in each column

Upvotes: 0

Related Questions