Sorting numbers as text

[Guest]

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 it’s 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 I’m not sure what I’m
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 I’m missing
something here quite simply but I would appreciate any suggestions.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.