Replace is re-formating the data.

  • Thread starter Thread starter Dave Mills
  • Start date Start date
D

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.
 
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
 
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.
 
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.
 
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.
 
Nothing to me.

Maybe lots for the OP.
Click to expand...

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.

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

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.
 
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.
Click to expand...
Maybe lots for the OP.
Click to expand...
Pete_UK wrote:
Click to expand...

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.
Click to expand...
 
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)
 
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.
Click to expand...

Now why didn't I think of that 2 years ago :-)
 
Back
Top