Sort numbers and letters sequentially

[camm]

I have a column of numbers and letters. How do I get them to sort sequentially? That is
how do I get the 33208 to come after the 399 and I do I get the 401 to come before the 40007?

For example
102
102g
263
318Q
33208
354Q
399
40007
40024Q
401

Any help will be greatly appreciated.

Thanks
camm

 

[John Caines]

Hi Camm,
there is a great video here that sorts via formulas,,,
http://www.5min.com/Video/How-to-Sort-Excel-Sheet-using-a-Formula-121289062

It's really worth watching

Why don't you try,,, (lets assume you column is A with your data in)
Go, Click to highlight Column A,, then Go,DATA,, then Go FILTER,,,
When you click on the filter,, there will be a Largest to smallest & Smallest To largest...
Choose, smallest to largest,, and it will sort your data
Hope this helps
best Regards
John Caines

 

[meldoc]

Hello

See the link.

http://support.microsoft.com/kb/322067

 

[camm]

John,
I went to Data Filter but there is not a way to go from largest to smallest it just gives me the auto or advanced filter function. Am I missing smething? I looked at the video but it did not address characters only numbers.

Thanks
camm

 

[camm]

meldoc,

I believe the numbers and letters I have are already text. They were downloaded from a database. When I tried the Text(A2,"###") it sorted the same way as my original sort (ie from left to right)

Thanks
camm

 

[meldoc]

If you want your list, sorted like this, change the column format to general.

Excel Workbook
	A
1	Your List
2	102
3	263
4	399
5	401
6	33208
7	40007
8	102g
9	318Q
10	354Q
11	40024Q
Sheet1

Excel 2010

 

[camm]

I would like it to sort like this

102
102G
263
318Q
354Q
399
401
33208
40007
40024Q

Thanks
camm

 

[meldoc]

Ok try this.

http://www.ozgrid.com/VBA/sort-alphanumeric.htm

 

[jasonb75]

May, or may not be practical depending on the size of the data range to sort

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>List</TD><TD>Helper</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">102</TD><TD style="TEXT-ALIGN: right">102</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">102g</TD><TD style="TEXT-ALIGN: right">102</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">263</TD><TD style="TEXT-ALIGN: right">263</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">318Q</TD><TD style="TEXT-ALIGN: right">318</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">354Q</TD><TD style="TEXT-ALIGN: right">354</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">399</TD><TD style="TEXT-ALIGN: right">399</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">401</TD><TD style="TEXT-ALIGN: right">401</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">33208</TD><TD style="TEXT-ALIGN: right">33208</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">40007</TD><TD style="TEXT-ALIGN: right">40007</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">40024Q</TD><TD style="TEXT-ALIGN: right">40024</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>{=--LEFT(A2,COUNT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sort by helper, then by list.

This suggestion assumes that all strings have leading numbers and trailing letters, 123abc, etc, things like a123bc or 12abc3 will fail.

 

[camm]

jasonb75

Your formula worked. The assumption that the strings have leading numbers and trailing letters was correct.

thanks
camm