Simple Sort question Text and number

[JimGunther]

I have a sheet with a column which contains text, then, a number.
When I sort, the result is not as expected.

see BELOW

I'm fine with the first part but, want 1 then 2, then 3 rather than 1 then 10, then, 100

Somehow I thinks this has been covered and, maybe even a basic function of Excel but, I haven't been able to 'figger it.

As always, TIA Jim

" 1004Fort991
1004Fort993
1004Fort994
1004Fort995
1004Fort996
1004Fort997
1004Fort998
1004Fort999
1004FortGone
1105ForGlob1
1105ForGlob10
1105ForGlob100
1105ForGlob1000
1105ForGlob1001
1105ForGlob1002
1105ForGlob1003
1105ForGlob1003"

 

[Akashwani]

Hi Jim,

I've probably misunderstood what you are requiring, but is this something like you want?....

Before sorting, copy the formula in A2 down

Excel Workbook
	A	B
2	1	1004Fort991
3	3	1004Fort993
4	4	1004Fort994
5	5	1004Fort995
6	6	1004Fort996
7	7	1004Fort997
8	8	1004Fort998
9	9	1004Fort999
10	e	1004FortGone
11	1	1105ForGlob1
12	0	1105ForGlob10
13	0	1105ForGlob100
14	0	1105ForGlob1000
15	1	1105ForGlob1001
16	2	1105ForGlob1002
17	3	1105ForGlob1003
18	3	1105ForGlob1003
Sheet4

After sorting by column A.....

Excel Workbook
	A	B
2	0	1105ForGlob10
3	0	1105ForGlob100
4	0	1105ForGlob1000
5	1	1004Fort991
6	1	1105ForGlob1
7	1	1105ForGlob1001
8	2	1105ForGlob1002
9	3	1004Fort993
10	3	1105ForGlob1003
11	3	1105ForGlob1003
12	4	1004Fort994
13	5	1004Fort995
14	6	1004Fort996
15	7	1004Fort997
16	8	1004Fort998
17	9	1004Fort999
18	e	1004FortGone
Sheet4

I hope that helps

Good luck

Ak

 

[JimGunther]

Thanks Ak,

I tried that but it didn't produce the result expected (and I'm not clear on the logic)

I probably didn't state my question very clearly.

My sheet consists of Fortune 1000 Companies and Forbes Global 2000 companies. Thus, the 1104Fort and the 1105ForGlob prefixs before the ranking numbers (i.e. 1 thru 1,000 and 1 thru 2000 respectivily).

I would like the column BELOW to sort as, 11,110,111, 1097,1099,1100,1101 etc.

BTW: I did 'fiigger an easy-enough work-around - simply doing a find "ForGlob" and replace "with nothing" which gice a pure numerical sort but - if possible - I'd like to sort the mixed in the text and numerical fashion (Now that I asked the question, somebody else might want to do this.).

1105ForGlob1097
1105ForGlob1099
1105ForGlob11
1105ForGlob11
1105ForGlob110
1105ForGlob1100
1105ForGlob1101
1105ForGlob1102
1105ForGlob1103
1105ForGlob1103
1105ForGlob1105
1105ForGlob1105
1105ForGlob1107
1105ForGlob1108
1105ForGlob1109
1105ForGlob111
1105ForGlob1110
1105ForGlob1110

 

[shift-del]

Hi Jim

This solution requires an array formula, so it will take some time to calculate 3000 items.
Columns B:F show how the formula in column G is build.
With ideas from http://www.excelformeln.de/formeln.html?welcher=101.

<table valign="middle" colspan="8" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="95,25pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="78pt"><col width="93,75pt"><col width="93,75pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="8" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">1004Fort991</td><td align="left">991</td><td align="left">0991</td><td align="left">1004</td><td align="left">Fort0991</td><td align="left">1004Fort0991</td><td align="left">1004Fort0991</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="left">1004Fort993</td><td align="left">993</td><td align="left">0993</td><td align="left">1004</td><td align="left">Fort0993</td><td align="left">1004Fort0993</td><td align="left">1004Fort0993</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="left">1004Fort994</td><td align="left">994</td><td align="left">0994</td><td align="left">1004</td><td align="left">Fort0994</td><td align="left">1004Fort0994</td><td align="left">1004Fort0994</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="left">1004Fort995</td><td align="left">995</td><td align="left">0995</td><td align="left">1004</td><td align="left">Fort0995</td><td align="left">1004Fort0995</td><td align="left">1004Fort0995</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="left">1004Fort996</td><td align="left">996</td><td align="left">0996</td><td align="left">1004</td><td align="left">Fort0996</td><td align="left">1004Fort0996</td><td align="left">1004Fort0996</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="left">1004Fort997</td><td align="left">997</td><td align="left">0997</td><td align="left">1004</td><td align="left">Fort0997</td><td align="left">1004Fort0997</td><td align="left">1004Fort0997</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td align="left">1004Fort998</td><td align="left">998</td><td align="left">0998</td><td align="left">1004</td><td align="left">Fort0998</td><td align="left">1004Fort0998</td><td align="left">1004Fort0998</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td align="left">1004Fort999</td><td align="left">999</td><td align="left">0999</td><td align="left">1004</td><td align="left">Fort0999</td><td align="left">1004Fort0999</td><td align="left">1004Fort0999</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td align="left">1004FortGone</td><td align="left">
</td><td align="left">
</td><td align="left">1004</td><td align="left">FortGone</td><td align="left">1004FortGone</td><td align="left">1004FortGone</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td align="left">1105ForGlob1</td><td align="left">1</td><td align="left">0001</td><td align="left">1105</td><td align="left">ForGlob0001</td><td align="left">1105ForGlob0001</td><td align="left">1105ForGlob0001</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">11</td><td align="left">1105ForGlob10</td><td align="left">10</td><td align="left">0010</td><td align="left">1105</td><td align="left">ForGlob0010</td><td align="left">1105ForGlob0010</td><td align="left">1105ForGlob0010</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">12</td><td align="left">1105ForGlob100</td><td align="left">100</td><td align="left">0100</td><td align="left">1105</td><td align="left">ForGlob0100</td><td align="left">1105ForGlob0100</td><td align="left">1105ForGlob0100</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">13</td><td align="left">1105ForGlob1000</td><td align="left">1000</td><td align="left">1000</td><td align="left">1105</td><td align="left">ForGlob1000</td><td align="left">1105ForGlob1000</td><td align="left">1105ForGlob1000</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">14</td><td align="left">1105ForGlob1001</td><td align="left">1001</td><td align="left">1001</td><td align="left">1105</td><td align="left">ForGlob1001</td><td align="left">1105ForGlob1001</td><td align="left">1105ForGlob1001</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">15</td><td align="left">1105ForGlob1002</td><td align="left">1002</td><td align="left">1002</td><td align="left">1105</td><td align="left">ForGlob1002</td><td align="left">1105ForGlob1002</td><td align="left">1105ForGlob1002</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">16</td><td align="left">1105ForGlob1003</td><td align="left">1003</td><td align="left">1003</td><td align="left">1105</td><td align="left">ForGlob1003</td><td align="left">1105ForGlob1003</td><td align="left">1105ForGlob1003</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">17</td><td align="left">1105ForGlob1003</td><td align="left">1003</td><td align="left">1003</td><td align="left">1105</td><td align="left">ForGlob1003</td><td align="left">1105ForGlob1003</td><td align="left">1105ForGlob1003</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>{=RIGHT(A1,COUNT(RIGHT(A1,COLUMN(1:1))*1))}</td></tr><tr><td>C1</td><td>=TEXT(B1,"0000")</td></tr><tr><td>D1</td><td>=LEFT(A1,4)</td></tr><tr><td>E1</td><td>=SUBSTITUTE(MID(A1,5,9^9),B1,C1)</td></tr><tr><td>F1</td><td>=D1&E1</td></tr><tr><td>G1</td><td>{=LEFT(A1,4)&SUBSTITUTE(MID(A1,5,9^9),RIGHT(A1,COUNT(RIGHT(A1,COLUMN(1:1))*1)),TEXT(RIGHT(A1,COUNT(RIGHT(A1,COLUMN(1:1))*1)),"0000"))}</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Contains array formula!
</td></tr><tr><td>Do not enter the curly brackets {}.</td></tr><tr><td>Enter the formula with CTRL-SHIFT-ENTER instead of just ENTER.</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[Akashwani]

Hi Jim,

I'm probably way out again with this, but I'm finding it interesting to try and resolve this!

Before sorting....

Excel Workbook
	D	E
2	1105ForGlob1097	15
3	1105ForGlob1099	15
4	1105ForGlob11	13
5	1105ForGlob11	13
6	1105ForGlob110	14
7	1105ForGlob1100	15
8	1105ForGlob1101	15
9	1105ForGlob1102	15
10	1105ForGlob1103	15
11	1105ForGlob1103	15
12	1105ForGlob1105	15
13	1105ForGlob1105	15
14	1105ForGlob1107	15
15	1105ForGlob1108	15
16	1105ForGlob1109	15
17	1105ForGlob111	14
18	1105ForGlob1110	15
19	1105ForGlob1110	15
Sheet4

After sorting

Excel Workbook
	D	E
2	1105ForGlob11	13
3	1105ForGlob11	13
4	1105ForGlob110	14
5	1105ForGlob111	14
6	1105ForGlob1097	15
7	1105ForGlob1099	15
8	1105ForGlob1100	15
9	1105ForGlob1101	15
10	1105ForGlob1102	15
11	1105ForGlob1103	15
12	1105ForGlob1103	15
13	1105ForGlob1105	15
14	1105ForGlob1105	15
15	1105ForGlob1107	15
16	1105ForGlob1108	15
17	1105ForGlob1109	15
18	1105ForGlob1110	15
19	1105ForGlob1110	15
Sheet4

If this is incorrect, can you provide a sample of what you want after sorting please

Where can I get a complete list from?

Ak

 

[JimGunther]

Ak,

I'm afraid I've taken the easy way out. ;(

I did a Find (1105ForGlob) and replaced with 110599. Then did a Find (1104Fort) and replaced with 1104 rendering the correctly ordered result BELOW -

BTW: I added the 99 so I could keep ForGlob and Fort results separate. "1104Fort" represented Fortune 1000 companies imported April 2011
"1105ForGlob" represented Forbes Global as of, well, May 2011.

Thanks - I will check back but, arrays and such (I think) are beyond my ability and, maybe, my need.

Just an aside: Is there any possibility that my sort "issues" were as a result of number not being saved as a number? Worse, I think the Forbes numbers may have been TEXT and the Fortune numbers as numbers. Just wondering?

Thanks again,Jim

11051993
11051994
11051995
11051995
11051997
11051997
11051999
11052000
100499006
100499013
100499034
100499042
100499049
100499049
100499059
100499064