Completely baffled on what should be simple

[Pat Hughes]

A friend of mine is trying to sort 1 column in a spreadsheet. The numbers
are 4 digit extension #'s. There are 707 numbers, rows, and only the 1
column. Nothing fancy. When we try to sort only the first 397 rows. The
header, Ext, is in the first row. Starting with the 398 row the sorting
starts all over again from there. We can't get them to sort all of the 707
extension numbers. We have tried changing everything. I added another
column with misc. numbers using the same number of digits and everything.
That row sorted fine with all 707 numbers. I tried sorting the 2 columns
together but only the bogus column sorted the whole column but the extension
numbers column only sorted up to the 397 row. This seems so silly and should
be easy. We've checked and unchecked lock to see if that had anything to do
with it but nothing.

Thanks,
Pat

 

[Trevor Shuttleworth]

Pat

where did the data come from ? It sounds like some are true numbers and
some are text values that look like numbers.

Depending on the version of Excel, I would have thought you'd see a warning
asking if you want to sort everything as numbers

Assuming your data is in column A starting in row 1, try:

cell B1: =--A1

and drag down. With a bit of luck you'll end up with column B having a list
of true numbers.

Now sort on column B. You could Copy and Paste Special | Values if you want
to lose the formulae.

Regards

Trevor

 

[Pat Hughes]

Thanks Trevor,
It didn't work. I am in Excell 2000. I tried putting cell B1 as =--A1 but
I just got an error message. I tried B1 as =-A1 but the same results. We've
tried changing the format for the numbers to be all text and all numbers but
that didn't work.

Pat

 

[Pete_UK]

I have Excel 2k and =--A1 in B1 worked for me, with both numbers and
text-looking-like-numbers in A1. However, if you want to try something
different, put this formula in B1:

=VALUE(A1)

and copy down.

Hope this helps.

Pete

 

[Trevor Shuttleworth]

Pat

what error message ? If you got #VALUE!, I guess there are non-displaying
alpha characters in the cells. I suspect that Pete's suggestion would give
the same result if that's the case.

You didn't say where the data came from. Was it downloaded ?

If the data is left aligned,the cell has something other than numeric data
in it.

Try putting: =LEN(A1) in cell C1 and dragging down. If the length of
data is not the same as the visible number of digits, you've got some
non-display characters in there.

Regards

Trevor

 

[Pat Hughes]

Thank y'all,
I figured out why =--A1 didn't work. It was because in A1 was the header
Extensions. So I did =--A2 and that worked. The only problem though is that
for the extensions that have either one or two 0's as the first numbers, the
0's are taken out. We need the extensions to show 0067, 0324, etc. After I
sorted putting them in number format, I changed them to text file. Then I
did another sort and it went back to the previous problem. So what I think
is happening is for some reason the sort w/ text format formats the first 378
numbers from 1044 to 9814, then starts again in row 379 with 0067 and goes to
9972. This confuses me because I think because of it being in text format
that 0 would come after 9. But I don't see why the rest of the numbers
wouldn't be before the numbers that start with a 0. But that still wouldn't
help because we need all the extensions in order.
I hope I'm explaining this correctly.
I really appreciate your help.
Thanks,
Pat

 

[Dave Peterson]

When I have text numbers masquerading as number numbers, I'll convert them to
real numbers by:

Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add.

And if I need to show leading zeros, I can use a custom format

format|Cells|Number tab|custom category
and type:
0000
in the "type" box

0000 will make sure that all numbers display at least 4 digits.

========
If you want to check any individual cell to see if it's text or number, you can
use a formula like:
=istext(a1)
or
=isnumber(a1)

If you know how many cells you're inspecting, you can use:
=count(a1:a10)
to count the numbers in those cells
=counta(a1:a10)
will count anything (numbers, text, formulas--including formulas that evaluate
to "")

 

[Pat Hughes]

Thanks. That worked perfectly and so simple. I can't believe I didn't think
of it.
Thanks to everyone for helping me.
Pat

 

[Pat Hughes]

Sorry, but now I can't get it to work. My friend first tried it and by
changing the format to custom 0000 and then sorting. But when he went out of
the program and then went back in it wouldn't work. I checked mine and
minewas still ok. But when I tried changing the column back to text and did
the sort (to put it back to where my friend originally had it) and then did
the same thing I previously did, changing format to custom 0000 and then
sort, it didn't work. I also looked in microsoft help and found the
following for sort order
"Alphanumeric sort When you sort alphanumeric text, Excel sorts left to
right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "


This would make me think that it should work being in text with 0 being first.

I am going to try and insert the file but don't know if I can insert files
in here.

Thanks,
Pat

Ext
1044
1550
2121
2122
2330
2333
2335
2337
2338
2341
2342
2356
2357
2359
2360
2361
2363
2365
2369
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2522
2523
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2550
2551
2552
2553
2554
2555
2556
2557
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2571
2572
2573
2576
2578
2579
2580
2581
2582
2583
2585
2586
2588
2589
2591
2592
2593
2594
2596
2597
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2619
2620
2622
2623
2624
2625
2626
2628
2629
2630
2631
2632
2633
2634
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2649
2650
2652
2653
2657
2658
2659
2660
2661
2662
2663
2664
2666
2667
2668
2669
2670
2671
2672
2673
2674
2676
2677
2678
2679
2680
2690
2692
2699
2822
2824
2825
2827
2828
2829
2833
2834
2838
2839
2840
2844
2846
2849
2940
2942
2943
2944
2945
2947
2948
2949
2961
2962
2963
2970
2971
2973
2975
2976
2977
2979
2982
2983
2984
2985
2988
2989
2990
2992
2994
3140
3149
3150
3151
3290
3291
3292
3293
3296
3297
3298
3614
4367
4386
5410
5450
5451
5452
5453
5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
5469
5470
5471
5472
5473
5478
5479
5481
5482
5483
5486
5487
5489
5490
5494
5496
5497
5499
6332
6333
6334
6335
6336
6337
6339
6340
6341
6342
6343
6344
6345
6346
6347
6364
6405
6407
6409
6410
6411
6412
6414
6415
6416
6417
6418
6419
6420
6424
6425
6430
6440
6485
6486
6487
6488
6490
6814
6815
6816
6817
6818
6819
6821
6822
6823
6824
6825
6826
6827
6828
6829
6830
6831
6832
6833
6834
6835
6836
6837
6838
6840
6841
6842
6843
6844
6845
6846
6847
6848
6849
6850
6851
6852
6853
6854
6855
6856
6857
6858
6860
6861
6862
6863
6864
6865
6866
6867
6869
6870
6871
6872
6873
6874
6875
6876
6877
6878
6879
6881
6882
6883
6884
6885
6888
6889
6890
6891
6892
6893
6894
6896
6897
6898
8776
9000
9001
9125
9126
9127
9128
9776
9814****
0067****
0118
0193
0218
0227
0291
0334
0373
0534
0535
0587
0716
0736
0771
0787
0805
0944
0950
0954
0966
1052
1193
1208
1264
1386
1403
1469
1532
1621
1664
1809
1812
1840
1996
2031
2042
2064
2113
2131
2199
2223
2326
2332
2334
2344
2345
2346
2347
2348
2352
2353
2355
2366
2395
2463
2521
2524
2529
2549
2556
2558
2570
2574
2575
2577
2584
2595
2627
2634
2635
2645
2654
2655
2665
2675
2681
2682
2683
2684
2685
2685
2686
2687
2688
2689
2694
2695
2696
2698
2820
2821
2823
2830
2831
2836
2841
2842
2843
2920
2941
2946
2961
2964
2965
2966
2967
2968
2969
2972
2974
2977
2978
2980
2986
2987
2993
2997
2999
3052
3053
3056
3059
3141
3142
3143
3144
3145
3146
3147
3148
3152
3153
3154
3155
3156
3157
3158
3159
3235
3488
3584
4030
4200
4200
4287
4322
4323
4324
4325
4516
4517
4551
4552
4558
4559
4933
5057
5281
5282
5283
5296
5310
5362
5474
5475
5491
5492
5495
5513
5607
5827
5890
5890
5893
6206
6300
6331
6338
6348
6349
6400
6401
6402
6403
6404
6406
6408
6413
6421
6422
6423
6426
6427
6428
6429
6431
6432
6433
6434
6435
6436
6437
6438
6439
6441
6442
6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453
6454
6455
6456
6457
6458
6459
6460
6461
6462
6464
6465
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6491
6492
6493
6494
6495
6495
6497
6498
6499
6695
6810
6811
6813
6820
6839
6880
6886
6887
6895
7129
7425
7479
7483
7665
7764
8012
8014
8024
8061
8350
8709
8721
8724
8740
8759
8794
8957
8971
9002
9028
9061
9236
9264
9299
9335
9343
9363
9626
9628
9635
9660
9675
9717
9740
9744
9748
9774
9853
9853
9861
9966
9971
9972

I couldn't insert the file so I did a copy paste. This column is formatted
as custom 0000. This is the result of my sort. I don't understand why it
won't sort correctly now with the format as 0000 but also I don't understand
why it won't sort as all being text.
Thanks,
Pat

 

[Dave Peterson]

Did your friend save the file after he made the changes (before he closed and
reopened the file)?

And remember just changing the format of the cell won't change the underlying
value.

Sorry, but now I can't get it to work. My friend first tried it and by
changing the format to custom 0000 and then sorting. But when he went out of
the program and then went back in it wouldn't work. I checked mine and
minewas still ok. But when I tried changing the column back to text and did
the sort (to put it back to where my friend originally had it) and then did
the same thing I previously did, changing format to custom 0000 and then
sort, it didn't work. I also looked in microsoft help and found the
following for sort order
"Alphanumeric sort When you sort alphanumeric text, Excel sorts left to
right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "

This would make me think that it should work being in text with 0 being first.

I am going to try and insert the file but don't know if I can insert files
in here.

Thanks,
Pat

Ext
1044
1550
2121
2122
2330
2333
2335
2337
2338
2341
2342
2356
2357
2359
2360
2361
2363
2365
2369
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2522
2523
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2550
2551
2552
2553
2554
2555
2556
2557
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2571
2572
2573
2576
2578
2579
2580
2581
2582
2583
2585
2586
2588
2589
2591
2592
2593
2594
2596
2597
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2619
2620
2622
2623
2624
2625
2626
2628
2629
2630
2631
2632
2633
2634
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2649
2650
2652
2653
2657
2658
2659
2660
2661
2662
2663
2664
2666
2667
2668
2669
2670
2671
2672
2673
2674
2676
2677
2678
2679
2680
2690
2692
2699
2822
2824
2825
2827
2828
2829
2833
2834
2838
2839
2840
2844
2846
2849
2940
2942
2943
2944
2945
2947
2948
2949
2961
2962
2963
2970
2971
2973
2975
2976
2977
2979
2982
2983
2984
2985
2988
2989
2990
2992
2994
3140
3149
3150
3151
3290
3291
3292
3293
3296
3297
3298
3614
4367
4386
5410
5450
5451
5452
5453
5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
5469
5470
5471
5472
5473
5478
5479
5481
5482
5483
5486
5487
5489
5490
5494
5496
5497
5499
6332
6333
6334
6335
6336
6337
6339
6340
6341
6342
6343
6344
6345
6346
6347
6364
6405
6407
6409
6410
6411
6412
6414
6415
6416
6417
6418
6419
6420
6424
6425
6430
6440
6485
6486
6487
6488
6490
6814
6815
6816
6817
6818
6819
6821
6822
6823
6824
6825
6826
6827
6828
6829
6830
6831
6832
6833
6834
6835
6836
6837
6838
6840
6841
6842
6843
6844
6845
6846
6847
6848
6849
6850
6851
6852
6853
6854
6855
6856
6857
6858
6860
6861
6862
6863
6864
6865
6866
6867
6869
6870
6871
6872
6873
6874
6875
6876
6877
6878
6879
6881
6882
6883
6884
6885
6888
6889
6890
6891
6892
6893
6894
6896
6897
6898
8776
9000
9001
9125
9126
9127
9128
9776
9814****
0067****
0118
0193
0218
0227
0291
0334
0373
0534
0535
0587
0716
0736
0771
0787
0805
0944
0950
0954
0966
1052
1193
1208
1264
1386
1403
1469
1532
1621
1664
1809
1812
1840
1996
2031
2042
2064
2113
2131
2199
2223
2326
2332
2334
2344
2345
2346
2347
2348
2352
2353
2355
2366
2395
2463
2521
2524
2529
2549
2556
2558
2570
2574
2575
2577
2584
2595
2627
2634
2635
2645
2654
2655
2665
2675
2681
2682
2683
2684
2685
2685
2686
2687
2688
2689
2694
2695
2696
2698
2820
2821
2823
2830
2831
2836
2841
2842
2843
2920
2941
2946
2961
2964
2965
2966
2967
2968
2969
2972
2974
2977
2978
2980
2986
2987
2993
2997
2999
3052
3053
3056
3059
3141
3142
3143
3144
3145
3146
3147
3148
3152
3153
3154
3155
3156
3157
3158
3159
3235
3488
3584
4030
4200
4200
4287
4322
4323
4324
4325
4516
4517
4551
4552
4558
4559
4933
5057
5281
5282
5283
5296
5310
5362
5474
5475
5491
5492
5495
5513
5607
5827
5890
5890
5893
6206
6300
6331
6338
6348
6349
6400
6401
6402
6403
6404
6406
6408
6413
6421
6422
6423
6426
6427
6428
6429
6431
6432
6433
6434
6435
6436
6437
6438
6439
6441
6442
6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453
6454
6455
6456
6457
6458
6459
6460
6461
6462
6464
6465
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6491
6492
6493
6494
6495
6495
6497
6498
6499
6695
6810
6811
6813
6820
6839
6880
6886
6887
6895
7129
7425
7479
7483
7665
7764
8012
8014
8024
8061
8350
8709
8721
8724
8740
8759
8794
8957
8971
9002
9028
9061
9236
9264
9299
9335
9343
9363
9626
9628
9635
9660
9675
9717
9740
9744
9748
9774
9853
9853
9861
9966
9971
9972

I couldn't insert the file so I did a copy paste. This column is formatted
as custom 0000. This is the result of my sort. I don't understand why it
won't sort correctly now with the format as 0000 but also I don't understand
why it won't sort as all being text.
Thanks,
Pat

Thanks. That worked perfectly and so simple. I can't believe I didn't think
of it.
Thanks to everyone for helping me.
Pat

 

[Pat Hughes]

Ok, sorry for the confusion. I think I have it. I had to do the

Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add

to the column. THen do the sort. Then do the custom 0000 format.

I thought by just changing the format would do the trick but it doesn't. So
is it everytime you change the format you have to do the
"Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add"
in order to change the value to numbers? Do I have to do something similar
to make sure when I change cells from number to text to make sure the value
is correct. I guess I am confused about this. I've used excel a lot for
about 15 years but I never had this problem. Thanks so much for your
patience.
I do still wonder why if I have the cells formated as text that the sort
wouldn't work because of what I read in the Microsoft Excel Help

"Alphanumeric sort
When you sort alphanumeric text, Excel sorts left to

right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "

This would make me think that it should work being in text with 0 being
first.
Thanks for all your help.
Pat

 

[Dave Peterson]

You're sorting the values--not the format.

And the custom format is used to make it look pretty--not to change the number
value back to text.

If you really want the values re-converted to Text (why???), you could use a
helper column filled with formulas like:

=text(a1,"0000")
(and drag down.)

I think I'd convert that column to real numbers, then format the whole column
using a custom format of 0000 and walk away happy.

Is there some reason you have to have the values converted back to text?

Ok, sorry for the confusion. I think I have it. I had to do the

Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add

to the column. THen do the sort. Then do the custom 0000 format.

I thought by just changing the format would do the trick but it doesn't. So
is it everytime you change the format you have to do the
"Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add"
in order to change the value to numbers? Do I have to do something similar
to make sure when I change cells from number to text to make sure the value
is correct. I guess I am confused about this. I've used excel a lot for
about 15 years but I never had this problem. Thanks so much for your
patience.
I do still wonder why if I have the cells formated as text that the sort
wouldn't work because of what I read in the Microsoft Excel Help

"Alphanumeric sort
When you sort alphanumeric text, Excel sorts left to

right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "

This would make me think that it should work being in text with 0 being
first.
Thanks for all your help.
Pat

 

[Pat Hughes]

Thanks,
I'll check with my friend when he gets back from lunch. I think he had some
coluns with text in front of the numbers and then the one column he showed me
had no text but was formatted as text.

You're sorting the values--not the format.

And the custom format is used to make it look pretty--not to change the number
value back to text.

If you really want the values re-converted to Text (why???), you could use a
helper column filled with formulas like:

=text(a1,"0000")
(and drag down.)

I think I'd convert that column to real numbers, then format the whole column
using a custom format of 0000 and walk away happy.

Is there some reason you have to have the values converted back to text?

Ok, sorry for the confusion. I think I have it. I had to do the

Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add

to the column. THen do the sort. Then do the custom 0000 format.

I thought by just changing the format would do the trick but it doesn't. So
is it everytime you change the format you have to do the
"Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add"
in order to change the value to numbers? Do I have to do something similar
to make sure when I change cells from number to text to make sure the value
is correct. I guess I am confused about this. I've used excel a lot for
about 15 years but I never had this problem. Thanks so much for your
patience.
I do still wonder why if I have the cells formated as text that the sort
wouldn't work because of what I read in the Microsoft Excel Help

"Alphanumeric sort
When you sort alphanumeric text, Excel sorts left to

right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "

This would make me think that it should work being in text with 0 being
first.
Thanks for all your help.
Pat