Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting numbers as text
Being a novice with VBA I thought I would try my hand at writing a simple
program for my inventory. It seems to work okay (eventually) with the exception of how it sorts. I want to sort by part numbers, which are numbers of varying lengths, or a combination numbers and letters also different lengths. I tried creating a custom sort by doing it as a macro then copying and pasting into my program but its not doing exactly what I was hoping. My problem is that when I enter a part number for example 3661 the sort routine in my program place it before number say 189912 obviously because of its value. What I want to achieve is numbers sorted from the first digit then the next and so on. I.E. 1889912, 1889913, 1901, 19014, 2101, 21981 AN174-10, AN174-11 etc, From a previous question I submitted along these line I was told to enter the numbers as text then they will be sorted alphabetically. On a test I did this seemed to do the trick but now I need to now how I can convert approximately 1000 numbers that I have already entered into text €“quite easy doing it the other way. Second, for the part numbers I will need to add in the future I tried modifying my program but Im not sure what Im doing wrong. I changed the €˜enter portion of my program from Cells(intRowCounter, 1) = txtPartNumber.Value to = txtPartNumber.Text but it would appear that they are still entered as a number and not text, making the sort routine sort by value and not alphabetically. Chances are Im missing something here quite simply but I would appreciate any suggestions. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting numbers as text
Cells(intRowCounter, 1) = "'" & txtPartNumber.Value
convert number sub ConvertNumberstoText() Dim cell as Range, rng as Range set rng = columns(1).SpecialCells(xlConstants,xlNumbers) for each cell in rng cell.value = "'" & cell.Text Next End Sub change columns(1) to indicate the column to process. -- Regards, Tom Ogilvy "Steve Stanley" wrote: Being a novice with VBA I thought I would try my hand at writing a simple program for my inventory. It seems to work okay (eventually) with the exception of how it sorts. I want to sort by part numbers, which are numbers of varying lengths, or a combination numbers and letters also different lengths. I tried creating a custom sort by doing it as a macro then copying and pasting into my program but its not doing exactly what I was hoping. My problem is that when I enter a part number for example 3661 the sort routine in my program place it before number say 189912 obviously because of its value. What I want to achieve is numbers sorted from the first digit then the next and so on. I.E. 1889912, 1889913, 1901, 19014, 2101, 21981 AN174-10, AN174-11 etc, From a previous question I submitted along these line I was told to enter the numbers as text then they will be sorted alphabetically. On a test I did this seemed to do the trick but now I need to now how I can convert approximately 1000 numbers that I have already entered into text €“quite easy doing it the other way. Second, for the part numbers I will need to add in the future I tried modifying my program but Im not sure what Im doing wrong. I changed the €˜enter portion of my program from Cells(intRowCounter, 1) = txtPartNumber.Value to = txtPartNumber.Text but it would appear that they are still entered as a number and not text, making the sort routine sort by value and not alphabetically. Chances are Im missing something here quite simply but I would appreciate any suggestions. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting numbers as text
Thanks Tom it worked great. Do you have any suggestions on how I can make it
sort and list alphanumerically - parts numbers beginning with letters first. I've tried several times to create a custom sort list but it still list part numbers starting with a number first and those starting with letters second... or do I need to post another question? "Tom Ogilvy" wrote: Cells(intRowCounter, 1) = "'" & txtPartNumber.Value convert number sub ConvertNumberstoText() Dim cell as Range, rng as Range set rng = columns(1).SpecialCells(xlConstants,xlNumbers) for each cell in rng cell.value = "'" & cell.Text Next End Sub change columns(1) to indicate the column to process. -- Regards, Tom Ogilvy "Steve Stanley" wrote: Being a novice with VBA I thought I would try my hand at writing a simple program for my inventory. It seems to work okay (eventually) with the exception of how it sorts. I want to sort by part numbers, which are numbers of varying lengths, or a combination numbers and letters also different lengths. I tried creating a custom sort by doing it as a macro then copying and pasting into my program but its not doing exactly what I was hoping. My problem is that when I enter a part number for example 3661 the sort routine in my program place it before number say 189912 obviously because of its value. What I want to achieve is numbers sorted from the first digit then the next and so on. I.E. 1889912, 1889913, 1901, 19014, 2101, 21981 AN174-10, AN174-11 etc, From a previous question I submitted along these line I was told to enter the numbers as text then they will be sorted alphabetically. On a test I did this seemed to do the trick but now I need to now how I can convert approximately 1000 numbers that I have already entered into text €“quite easy doing it the other way. Second, for the part numbers I will need to add in the future I tried modifying my program but Im not sure what Im doing wrong. I changed the €˜enter portion of my program from Cells(intRowCounter, 1) = txtPartNumber.Value to = txtPartNumber.Text but it would appear that they are still entered as a number and not text, making the sort routine sort by value and not alphabetically. Chances are Im missing something here quite simply but I would appreciate any suggestions. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting numbers as text
Why not sort them as they come out, that should split the Alpha numeric from
the numeric, then include in your code, code to identify that split, then copy one section below the otherin your code to achieve the sort you want. If that isn't acceptable, you will need to set up a dummy column adjacent to your data with a formula like =if(iserror(Left(A2,1)*1),1,2) & A2 and sort on that column. That would also remedy the requirement to store everything as Text as this formula will produce a text string output. -- Regards, tom Ogilvy "Steve Stanley" wrote: Thanks Tom it worked great. Do you have any suggestions on how I can make it sort and list alphanumerically - parts numbers beginning with letters first. I've tried several times to create a custom sort list but it still list part numbers starting with a number first and those starting with letters second... or do I need to post another question? "Tom Ogilvy" wrote: Cells(intRowCounter, 1) = "'" & txtPartNumber.Value convert number sub ConvertNumberstoText() Dim cell as Range, rng as Range set rng = columns(1).SpecialCells(xlConstants,xlNumbers) for each cell in rng cell.value = "'" & cell.Text Next End Sub change columns(1) to indicate the column to process. -- Regards, Tom Ogilvy "Steve Stanley" wrote: Being a novice with VBA I thought I would try my hand at writing a simple program for my inventory. It seems to work okay (eventually) with the exception of how it sorts. I want to sort by part numbers, which are numbers of varying lengths, or a combination numbers and letters also different lengths. I tried creating a custom sort by doing it as a macro then copying and pasting into my program but its not doing exactly what I was hoping. My problem is that when I enter a part number for example 3661 the sort routine in my program place it before number say 189912 obviously because of its value. What I want to achieve is numbers sorted from the first digit then the next and so on. I.E. 1889912, 1889913, 1901, 19014, 2101, 21981 AN174-10, AN174-11 etc, From a previous question I submitted along these line I was told to enter the numbers as text then they will be sorted alphabetically. On a test I did this seemed to do the trick but now I need to now how I can convert approximately 1000 numbers that I have already entered into text €“quite easy doing it the other way. Second, for the part numbers I will need to add in the future I tried modifying my program but Im not sure what Im doing wrong. I changed the €˜enter portion of my program from Cells(intRowCounter, 1) = txtPartNumber.Value to = txtPartNumber.Text but it would appear that they are still entered as a number and not text, making the sort routine sort by value and not alphabetically. Chances are Im missing something here quite simply but I would appreciate any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting numbers and numbers that contain text in excel | Excel Discussion (Misc queries) | |||
sorting text & numbers | Excel Discussion (Misc queries) | |||
Help sorting text as numbers | Excel Worksheet Functions | |||
sorting numbers as text | Excel Programming | |||
SORTING TEXT AND NUMBERS | Excel Worksheet Functions |