Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 Pat Hughes wrote: 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 wrote: 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
Thanks. That worked perfectly and so simple. I can't believe I didn't think
of it. Thanks to everyone for helping me. Pat :) "Dave Peterson" wrote: 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 wrote: 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 "Pat Hughes" wrote: Thanks. That worked perfectly and so simple. I can't believe I didn't think of it. Thanks to everyone for helping me. Pat :) "Dave Peterson" wrote: 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 wrote: 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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. Pat Hughes wrote: 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 "Pat Hughes" wrote: Thanks. That worked perfectly and so simple. I can't believe I didn't think of it. Thanks to everyone for helping me. Pat :) "Dave Peterson" wrote: 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 wrote: 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 "Trevor Shuttleworth" wrote: 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" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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? Pat Hughes wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Completely baffled on what should be simple
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. "Dave Peterson" wrote: 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? Pat Hughes wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple, but not for me...help | Excel Worksheet Functions | |||
Simple If, Then formula for excel | Excel Discussion (Misc queries) | |||
Simple Formula (I thought) | Excel Worksheet Functions | |||
Looking to create a simple user form with lookup | Excel Discussion (Misc queries) | |||
simple if then function | Excel Worksheet Functions |