Sorting Alphanumeric Data in Excel

Thread Starter

Brownout

Joined Jan 10, 2012
2,390
I have vector data that contains characters and numerals, ie

vector<0>
vector<1>
...
vector<10>
vector<11>
....
vector<20>
vector<21>
ect.

If I sort this data in Excel, I get this?

vector<0>
vector<1>
vector<10>
vector<11>
...
vector<2>
vector<20>
vector<21>
etc.

Anyone ever get this to work correctly?
 

BillB3857

Joined Feb 28, 2009
2,570
Change the <1> to <01>, the <2> to <02>. etc and see what happens. (add leading zeros to fill out the maximum length of the count, ie 0001 if the max count will be 9999)
 

Thread Starter

Brownout

Joined Jan 10, 2012
2,390
I'll give that a try, however this data is generated, not created. I get it from software tools I use for design place and route. I would much rather have a method that doens't involve hand-editing the data each time I run the tools, if one exists.
 

djsfantasi

Joined Apr 11, 2010
9,156
Copy the data into a new column. Use the Excel text to data feature to parse out only the number into its own column. Then, set that column to be a numerical data type and sort on it.

When using text to data, specify the less than symbol as your delimiter and also do not use / import the first column (containing "vector"). You may have to parse a second time with the greater than symbol to remove it as well.
After sorting, any work columns can be deleted.

Text to data in Excel is a great tool to process raw data and convert it into a more usable form. These steps can be saved into a macro, so the conversion and sorting can be done with a keystroke.
 
Last edited:

tshuck

Joined Oct 18, 2012
3,534
You can also make a custom sort...

Click sort, click the dropdown box under "Order", and select "Custom List...". There you enter in the proper, sorted list once, and it will sort according to this when selected.

It's a bit of a pain to make the list, but works...
 

LDC3

Joined Apr 27, 2013
924
I'll give that a try, however this data is generated, not created. I get it from software tools I use for design place and route. I would much rather have a method that doens't involve hand-editing the data each time I run the tools, if one exists.
I'm using OpenOffice so I'm not certain is this will work in Excel.

In the Find and Replace dialog box:
Rich (BB code):
Find - \<(\d)\>
Replace - 0$0
Click 'More Options' and select 'Regular Expressions'
Click 'Replace All'
There should be an equivalent method in Excel.
 

Thread Starter

Brownout

Joined Jan 10, 2012
2,390
Copy the data into a new column. Use the Excel text to data feature to parse out only the number into its own column. Then, set that column to be a numerical data type and sort on it.
I got this to work. Here is the equation I ended up with:

=VALUE(MID(B1,SEARCH("<",B1)+1,SEARCH(">",B1)-SEARCH("<",B1)-1))

this is for the first cell in the list, hence, the "B1". The other equations auto-increment as I drag them through.


Narly, eh?
 

Thread Starter

Brownout

Joined Jan 10, 2012
2,390
I'm using OpenOffice so I'm not certain is this will work in Excel.

In the Find and Replace dialog box:
Rich (BB code):
Find - \<(\d)\>
Replace - 0$0
Click 'More Options' and select 'Regular Expressions'
Click 'Replace All'
There should be an equivalent method in Excel.
My equivalent method (see above) is alot more complicated. I'll give this a try too.

Edit: can't find an option for regualr expressions. I'll try this with Open Office.
 

Thread Starter

Brownout

Joined Jan 10, 2012
2,390
Copy the data into a new column. Use the Excel text to data feature to parse out only the number into its own column. Then, set that column to be a numerical data type and sort on it.
Brownout said:
I got this to work. Here is the equation I ended up with:

=VALUE(MID(B1,SEARCH("<",B1)+1,SEARCH(">",B1)-SEARCH("<",B1)-1))

this is for the first cell in the list, hence, the "B1". The other equations auto-increment as I drag them through.


Narly, eh?
Ok, so I have something called "Text to Columns" which works exactly as you have described. Both of these methods work well.
 
Top