sort text numbers ascending

[rjmdc]

hi
thousands of check numbers and direct deposits and quickbooks
i mergedall the various statements together via PQ
since they are not all numbers some say DD or QB i turned check number column into text
problem happens when i sort
i get 1, 100, 1000, 10000 then the 2's
this makes it impossible to find any checks
is there a way to "fool" the text and have it sort as a number?

 

[arthurbr]

Why not make them real numbers ?
Enter 0 somewhere and copy
Select the entire number range - Copy - Paste special - Add

 

[rjmdc]

i cant make them real numbers as the ones that have QB or DD or any other letters fail in PQ as they are not numbers

 

[rjmdc]

hi has anyone come up with a solution to help me?

 

[rjmdc]

does anyone have anything to offer?

 

[aRandomHelper]

is there a way to "fool" the text and have it sort as a number?

When you do the sorting, don't Excel ask you something like, 'Sort anything that looks like a number, as a number' ?

Another way is to have a separate helper column with a formula like:

Book1.xlsm
	A	B
1	1029	000001029
2	1360	000001360
3	1557	000001557
4	1588	000001588
5	2241	000002241
6	2383	000002383
7	2442	000002442
8	2579	000002579
9	3072	000003072
10	3206	000003206
11	3406	000003406
12	376	000000376
13	3939	000003939
14	4288	000004288
15	4944	000004944
16	846	000000846
17	871	000000871
18	925	000000925
19	DD	DD
20	QB	QB
Sheet4

Cell Formulas
Range	Formula
B1:B20	B1	=TEXT(A1,"000000000")

Then, sort on column B:

Book1.xlsm
	A	B
1	376	000000376
2	846	000000846
3	871	000000871
4	925	000000925
5	1029	000001029
6	1360	000001360
7	1557	000001557
8	1588	000001588
9	2241	000002241
10	2383	000002383
11	2442	000002442
12	2579	000002579
13	3072	000003072
14	3206	000003206
15	3406	000003406
16	3939	000003939
17	4288	000004288
18	4944	000004944
19	DD	DD
20	QB	QB
Sheet4

Cell Formulas
Range	Formula
B1:B20	B1	=TEXT(A1,"000000000")

 

[rjmdc]

When you do the sorting, don't Excel ask you something like, 'Sort anything that looks like a number, as a number' ?

Another way is to have a separate helper column with a formula like:

Book1.xlsm
	A	B
1	1029	000001029
2	1360	000001360
3	1557	000001557
4	1588	000001588
5	2241	000002241
6	2383	000002383
7	2442	000002442
8	2579	000002579
9	3072	000003072
10	3206	000003206
11	3406	000003406
12	376	000000376
13	3939	000003939
14	4288	000004288
15	4944	000004944
16	846	000000846
17	871	000000871
18	925	000000925
19	DD	DD
20	QB	QB
Sheet4

Cell Formulas
Range	Formula
B1:B20	B1	=TEXT(A1,"000000000")

Then, sort on column B:

Book1.xlsm
	A	B
1	376	000000376
2	846	000000846
3	871	000000871
4	925	000000925
5	1029	000001029
6	1360	000001360
7	1557	000001557
8	1588	000001588
9	2241	000002241
10	2383	000002383
11	2442	000002442
12	2579	000002579
13	3072	000003072
14	3206	000003206
15	3406	000003406
16	3939	000003939
17	4288	000004288
18	4944	000004944
19	DD	DD
20	QB	QB
Sheet4

Cell Formulas
Range	Formula
B1:B20	B1	=TEXT(A1,"000000000")

please explain?
excel doesnt ask me anything

 

[aRandomHelper]

Try using Data -> Sort, instead of the smaller quick icons 'Sort A to Z' or 'Sort Z to A'. Excel gives me a warning when I use Data->Sort on numbers stored as text.

If it still doesn't work then try the formula way on my previous post.

 

[rjmdc]

i dont undertsand the formula

 

[rjmdc]

wow thank you!
i didnt realise i should use the sort on home tab
then it asked sort if it looks like number as number
now everyhting is sorted correctly

can i add this to a macroupon activating the sheet?