sorting help

[peter]

Bear in mind, I am new to excel. Say I have a column with the
following entries:

2
1
2-a
3

When I use the "sort ascending" button on this column it comes up:

1
2
3
2-a

What I would like is the following:
1
2
2-a
3

Can anyone help?

Thanks,
Peter

 

[David McRitchie]

Hi Peter,
You would have to make sure that Excel knows that each
cell is text. Excel sorts numeric cells before text cells.
Format the column as text, and then reenter each cell
with F2 then Enter; or use a macro to effect the reentry.
http://www.mvps.org/dmcritchie/excel/join.htm#reenter
one of the macros there should help, probably the first one
under that topic.

 

[peter]

Thank you, David!

 

[peter]

OK, that worked for my example. In my actual spreadsheet, I am copying
data from one sheet to the next where I sort it. I wasn't able to get
it to work when I copied and pasted data. Do I format the original
sheet as text and the sheet on which it is sorted? Maybe I didn't try
hard enough, but I'm wondering if you have a quick answer.

 

[David McRitchie]

Hi Peter,
Since those are probably constants, that actually
would simplify that if that is all there is to it.

Format the output area as text, and copy the
input area, the paste as values (edit, paste special, values)

My Excel is 2000, just in case that doesn't work for you.

 

[peter]

Ok I think I got it this time. Thanks!

 

[peter]

It's still not working on my actual sheet. What I do, step by step,
is:

1. I import a text file which is a list of data running vertically.
One entry per line.
2. On the sheet to which it is imported, I have set it up so certain
cells are assigned the contents of each imported cell, basically going
across.
3. I copy the contents of these cells into a new sheet and paste them
starting on the next available line.

I deleted the contents of all cells and formatted them to text, then
tried to import again. It still gave me the same problem of putting
"1-a" at the botttom of the sheet when I sorted. What am I missing? I
wonder if I should rebuild the sheets from scratch. I can do this but
it is time consuming so I'd like to know if you think that would work.
Thanks,
Peter

 

[David McRitchie]

If you import them again, then you have to start over again.

If the extension is .csv then change it the .txt so that the
text import wizard will give you a change to format the column
as text beforehand.

 

[peter]

Ok, David, I am making progress and I very much appreciate your help
The file is already a .txt. I did not think of formatting it using the
import wizard. That seems to solve my problem, but I won't know for
sure until I go to work tomorrow and test it. The text file changes
daily, so will I be able to format it once using the text import wizard
and then refresh it automatically, each day, with it adhering to the
text format? Secondly, I discovered another problem. When I format
the rows to text, it compares character by character, so 27 would be
placed above 3. I read the help file and found out why, but I did not
find out how to change this, so 3 would be before 27, and 1-a would be
between 1 and 2. Any thoughts?


Thanks,
Peter

 

[David McRitchie]

Hi Peter,
On a regular basis you would probably want to run a macro
to read in the file and format as you want, because the
input wizard is manual -- you can record a macro for the
entire process.

From you original note, I thought you were looking for a text
sort. Looks like you want the initial digits sorted numerically
and the rest sorted as text. Harlan Grove posted some neat
usage of Regular Expressions used within User Defined Functions.
I've tried to put that information on a web page, with some additional
references supplied by Harlan, and I wrote some code to help me
examine usages of Regular expressions.

Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
This will involve two additional helper columns.

 

[peter]

Ok, I'll have to have to look at that code and play around some. Thank
you for your help!