hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp

one method would be to copy the column of data paste it to the right of data
set with common rows. select the new column and data-text to column and use
"-" as your delimiter.
select all and sort on the two new columns

you could also write a macro if you have to do it very often

" wrote:

hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp

You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit<find
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp

I like your method It would be easily expandable to greater than -100

additionally after the sort, it could go backwards to initial format by
find -0 repace by -


"Ron Coderre" wrote:

You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit<find
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp

Thanks, BJ....I'm glad you liked it.


***********
Best Regards,
Ron

XL2002, WinXP


"bj" wrote:

I like your method It would be easily expandable to greater than -100

additionally after the sort, it could go backwards to initial format by
find -0 repace by -


"Ron Coderre" wrote:

You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit<find
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp

Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

On 11 jul, 15:04, Ron Coderre
wrote:
You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron

what does your macro say?

" wrote:

Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

On 11 jul, 15:04, Ron Coderre
wrote:
You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron

The visual basic code is:

Selection.Replace What:="-", Replacement:="-0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I can see what's going wrong, but I have no idea how I can correct
this...
Any help is welcome

Thnx,
Leon

Don't look for Excel to record *that* macro properly <g

Try this:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
If .Cells.Count = 1 Then
If MsgBox( _
Title:="Please Confirm", _
Prompt:="Only 1 cell selected...." _
& "OK to fix ALL cells in the worksheet?", _
Buttons:=vbQuestion + vbYesNo) _
= vbNo Then
Exit Sub
End If
Else
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End If
End With

End Sub
'--------End of Code-------

To run it....Select the range to be impacted...
Then.....[Alt]+[F8]....Select "FixDashNums"....Click [Run]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

On 11 jul, 15:04, Ron Coderre
wrote:
You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron

Ron,

for today: you're my hero!

it works just fine. Thanks a lot, this saves me tons of time\work!

best regards,
Leon

Well!....I sure munged up that code!
I started out intending to use Find/Replace....then changed my mind and with
with iterative code. (sorry)

This is what I SHOULD have posted:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End With

End Sub
'--------End of Code-------

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Don't look for Excel to record *that* macro properly <g

Try this:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
If .Cells.Count = 1 Then
If MsgBox( _
Title:="Please Confirm", _
Prompt:="Only 1 cell selected...." _
& "OK to fix ALL cells in the worksheet?", _
Buttons:=vbQuestion + vbYesNo) _
= vbNo Then
Exit Sub
End If
Else
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End If
End With

End Sub
'--------End of Code-------

To run it....Select the range to be impacted...
Then.....[Alt]+[F8]....Select "FixDashNums"....Click [Run]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

On 11 jul, 15:04, Ron Coderre
wrote:
You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron

the problem is earlier in the code, in that the selection must have all
selected, not just the ones with one character after the dash

" wrote:

The visual basic code is:

Selection.Replace What:="-", Replacement:="-0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I can see what's going wrong, but I have no idea how I can correct
this...
Any help is welcome

Thnx,
Leon

As I said, I have no idea how to write these things, but I can
somewhat read them. The latter looks more logical, but both work just
as well in the few small tests I ran them through.

well, thanks again!
Leon