Sorting texts and numbers as if it was all numbers

[Werner]

Hi,

I have a list of appartement numbers. Sometimes it's written 102 o
sometimes 102a and 102b.

E.G

102
103
104a
104b
105
(...)

Actually, if I record a macro to sort those valuesm it puts 104a an
104b at the end of the list, without considering them as numbers whic
are locally before 105. Note that it could be one, two, three or mor
digits. It would be a dynamic lenght. The letter too could be dynami
as well.

Is there a way to tell Excel/VBE to sort intelligently this list?

Thx!

Werner


Code
-------------------


Sub Sorting()

Rows("16:29").Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[Guest]

You need to create a new column (Call it sort) and make it the text values of
all of the numbers and text. Sort based on this new column and then delete
the column when you are done. Or just convert all of the numbers to text and
then sort.

 

[Werner]

It's logic, it could work easily. But the, how could I convert th
numbers into text? Do you have a vba code for that

 

[Werner]

I converted the numbers in text but it didn't sort correctly the numbers
which have a string character at the end. Maybe my sorting formula in
VBA needs a bit more of precision?


Code:
--------------------

With Worksheets("Formulaire")
'The line used to convert numbers into text
Range("A16:A" & lstRw).NumberFormat = "@"
Rows("16:" & lstRw).Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom

Range("A15").Select
End With

 

[Werner]

I've done a search in another forum, ozgrid.com forum and I have found a
post going back to july 20th 2004. Derk, an excel expert, has posted
back then an amazing search macro that works like magic. It resolved
instanly all my problems in an efficient way. Here's the link :

http://www.ozgrid.com/forum/showthread.php?t=22170&highlight=sort+text+numbers

I say thanks to Derk for that code!

Werner

 

[Tom Ogilvy]

The question you posted there doesn't make much sense to me. The sample
data you show is already sorted on both columns from what I can tell.