Sorting imported text

  • Thread starter Thread starter Leslie
  • Start date Start date
L

Leslie

I have a spreadsheet into which I've imported a great deal of data in two
columns only. I set out rows one, three and five of hundreds of such rows.
The cells in column A consist of place names. (The "C" following the place
name stands for "City", the "A" for "Area".) The cells in column C represent
the population of the city or area, as the case may be. I've formatted the
cells in column C as numbers.

Albury (C) 44887


Armidale Dumaresq (A) 24596


Ashfield (A) 40258


I want to sort the placenames in order of their population from highest to
lowest, showing their populations as well. I tried to sort the data on
column C in descending order, but nothing happened.

I fear that may be because of my method of importing the data. I got it by
first downloading the text only of a Web page which had the data, thoughnot
in a table. I then transferred the text to the worksheet.

Is there something I should be doing which would permit me to sort the data
as I want?

Thank you.
 
Dave,

Thank you very much for your reply.

Before seeing your post, I was able to get my data into a worksheet in a way
that avoided any HTML source material problems and so that changes my
question a bit.

I now have a column of cells, each of which has three characters at the
start and four characters at the end which I'd like to remove. However, the
bit in the middle I want to save is not of a constant length. Examples follow:

(C)4,88735.1
(A)24,59632.3
(A)140,25837.4

In the first cell, I want to save five characters (4,887), in the second six
(24,596) and in the third seven (140,258).

I've looked at worksheet functions, but haven't seen a way to reduce the
length of a string of characters by removing a fixed number of them from the
start or end of the string regardless of the string's length.

Can you point me to a way to do that?

Thanks again for your reply.

Leslie
 
You can use =mid() to return the characters:

=mid(a1,4,len(a1)-3-4)
this returns text.

If you want to return a real number, you can use:
=--mid(a1,4,len(a1)-3-4)

you could do the arithmetic in that formula, too:

=mid(a1,4,len(a1)-7)
or
=--mid(a1,4,len(a1)-7)
 
Dave,

Thanks so much for your latest reply.

It turns out I was wrong in my original post to say I had a list of hundreds
of place names--I only had a list of 165.

However, thanks to you, I now have a list of 165 places in the State of New
South Wales, Australia, sorted from largest population to smallest.

If you'd like to see it, just let me know :)

Best wishes,

Leslie
 
Thanks for the offer. But I think I'll decline at this time <vbg>.
Dave,

Thanks so much for your latest reply.

It turns out I was wrong in my original post to say I had a list of hundreds
of place names--I only had a list of 165.

However, thanks to you, I now have a list of 165 places in the State of New
South Wales, Australia, sorted from largest population to smallest.

If you'd like to see it, just let me know :)

Best wishes,

Leslie
Click to expand...
 
Back
Top