Replace is re-formating the data.

[Dave Mills]

I have a spreadsheet with a list of computer names in it. I need to sort the
rows by names but the names have a "-" in them. So in VBA I replace all the "-"
with "{=}", sort the rows and then replace the "{=}" with "-". This works like a
charm except when a Computer is named something like "SEP-01" the second replace
produces a value that is interpreted as a date and the name get stored as
01-Sep.

Any idea how I can get round this.

 

[Pete_UK]

I'm not sure why you need to replace the - with an = in the first
place, before sorting.

There is another hyphen character (soft hyphen) ­with a code of 173
that you could use when you replace the = back to a hyphen.

Hope this helps.

Pete

 

[Dave Peterson]

You could add a helper column, fill it full of formulas and then sort all the
data by that helper column.

And then delete the column when you're done.

=substitute(a2,"-","=")

will replace the hyphens with equal signs.

 

[Dave Peterson]

This is from xl2003's help for "Default sort orders"

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.

 

[Dave Peterson]

You could insert that helper column, copy|paste special|values into that column
and then use the edit|replace to fix the hyphens.

Then sort by that column and delete that column when you're done with it.

 

[Pete_UK]

Thanks Dave, but what's wrong with Computer-1 being sorted after
Computer1 ?

Pete

 

[Dave Peterson]

Nothing to me.

Maybe lots for the OP.

Thanks Dave, but what's wrong with Computer-1 being sorted after
Computer1 ?

Pete

 

[Dave Mills]

Nothing to me.

Maybe lots for the OP.

The computer names are "Room-sequentNo"
SO sorting

Room1-01
Room1-02
Room10-07
Room10-08
Room10-09
Room10-10
Room10-11

gives

Room10-07
Room10-08
Room10-09
Room1-01
Room10-10
Room10-11
Room1-02

Which is not helpful where there are Hundreds of rooms and computers.

 

[Dave Mills]

You could insert that helper column, copy|paste special|values into that column
and then use the edit|replace to fix the hyphens.

That would work but is a lot of effort. I did wonder if the "ReplaceFormat"
could help but I cant find much info on how it works other that when changing
all formats from one style to another. I don't see if/how if can be use to
control the insert format.

 

[Pete_UK]

I don't know how representative your example is, but if you highlight
those cells and CTRL-H (Find & Replace):

Find what: m1-
Replace with: m01-
Click Replace All

then you will have:

Room01-01
Room01-02
Room10-07
Room10-08
Room10-09
Room10-10
Room10-11

and this sorts as you would expect it to.

Hope this helps.

Pete

Nothing to me.

Maybe lots for the OP.

Pete_UK wrote:

The computer names are "Room-sequentNo"
SO sorting

Room1-01
Room1-02
Room10-07
Room10-08
Room10-09
Room10-10
Room10-11

gives

Room10-07
Room10-08
Room10-09
Room1-01
Room10-10
Room10-11
Room1-02

Which is not helpful where there are Hundreds of rooms and computers.

 

[Dave Peterson]

It's not really much more work than the way you're doing it now.

You copy|paste a column (new step)
Do edit|replace (same as before)
Sort data by this column (almost the same)
Delete the helper column (replaces the final edit|replace)

 

[Dave Mills]

I don't know how representative your example is, but if you highlight
those cells and CTRL-H (Find & Replace):

Find what: m1-
Replace with: m01-
Click Replace All

then you will have:

Room01-01
Room01-02
Room10-07
Room10-08
Room10-09
Room10-10
Room10-11

and this sorts as you would expect it to.

Hope this helps.

Now why didn't I think of that 2 years ago