CODEWITHSUNDEEP
excelsortingcolumnsorting
Joe Blough
Joe Blough

Reputation: 87

Sorting Excel columns in numeric order

I have several columns in Excel that looks like this:

Excel columns

Notice the column headers in the first row, 56g_flux_data39(1)1992, 56g_flux_data39(10)1992, 56g_flux_data39(11)1992, etc.

I want to sort them in numerical order, so 56g_flux_data39(1)1992, 56g_flux_data39(2)1992, 56g_flux_data39(3)1992, all the way to 56g_flux_data39(11)1992; but Excel's sorting function doesn't seem to recognize the numbers between the brackets (1). It wants to group the numbers beginning with 1 together, for example (1), (10), (11).

How do I get Excel to see the (10) and (11) as a ten and eleven and sort them in proper numerical order?

Thanks.

Upvotes: 0

Views: 54

Answers (2)

JulianG
JulianG

Reputation: 442

Create another row above your column title with the following formula. This will isolate the number, which can then be sorted according to its numerical value (tested, see screenshot below):

=LEFT(RIGHT(A3;LEN(A3)-16);LEN(A3)-21)

enter image description here

Upvotes: 1

user2512571
user2512571

Reputation: 53

Try putting a zero in front of the single digits. Pad with more zeros if you have higher numbers.

56g_flux_data39(01)1992, 56g_flux_data39(02)1992, 56g_flux_data39(03)1992, all the way to 56g_flux_data39(11)1992

Upvotes: 0

Related Questions