I have a column which i've set as a range. This range contains cells
with numbers only, and cells which contain a mixture of characters.
At the beginning, I sort the information in ascending order. I then
import data where it should go in the order. The problem I am running
into is that when I import data, it sees a number like 1234 as lower
than 5. But when I sort the information the next time, it puts the 5
in front of the 1234. If I were to add a character such as a dash
after each of the numbers. It would see 1234- as lower than 5- and
also see it as lower the next time I do a sort, which I would like it
to. Does what I'm saying make sense? Does anyone have any
suggestions?

It is seeing it all as text.

After importing it, select the column, and do a DataText To Columns, with
Fixed Width and finish.

They should all be numbers then and sort correctly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mcolson" wrote in message
ups.com...
I have a column which i've set as a range. This range contains cells
with numbers only, and cells which contain a mixture of characters.
At the beginning, I sort the information in ascending order. I then
import data where it should go in the order. The problem I am running
into is that when I import data, it sees a number like 1234 as lower
than 5. But when I sort the information the next time, it puts the 5
in front of the 1234. If I were to add a character such as a dash
after each of the numbers. It would see 1234- as lower than 5- and
also see it as lower the next time I do a sort, which I would like it
to. Does what I'm saying make sense? Does anyone have any
suggestions?

In Excel, I've tried setting all of the data to text for that column
and then sorting it. It still sorts the values as if they were
numbers, rather than comparing the first character, then the second
character ... as it does when sorting alphabetically. This wouldn't
be such a problem if I had just numbers, or just cells that contained
a mixture of characters (never just numbers).

Matt

On Feb 7, 10:46 am, "Bob Phillips" wrote:
It is seeing it all as text.

After importing it, select the column, and do a DataText To Columns, with
Fixed Width and finish.

They should all be numbers then and sort correctly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mcolson" wrote in message

ups.com...

I have a column which i've set as a range. This range contains cells
with numbers only, and cells which contain a mixture of characters.
At the beginning, I sort the information in ascending order. I then
import data where it should go in the order. The problem I am running
into is that when I import data, it sees a number like 1234 as lower
than 5. But when I sort the information the next time, it puts the 5
in front of the 1234. If I were to add a character such as a dash
after each of the numbers. It would see 1234- as lower than 5- and
also see it as lower the next time I do a sort, which I would like it
to. Does what I'm saying make sense? Does anyone have any
suggestions?

Somehow you need to pad the numbers with leading zeros so that all the
numbers have the same number of characters. Is it practicable within your
needs to do this? If so, you could put the following formula in a temporary
column next to the column of data to convert all the numbers only to text and
padded with leading zeros to provide a string length to whatever you want.
(Change the number of zeros between the inverted commas to alter the string
length.)
The formula will only convert the cells it can identify as numeric and
simply copy the other cells with alpha and alpha/numeric data. The formula
assumes original data is in Cell A1 and formula in cell B1.

=IF(ISNUMBER(A1),VALUE(A1),A1)

Copy the formula to the full length of the data and when you have done this,
select the column with the formulas and copy, paste special, values to remove
the formulas and leave the values then and copy and paste it over the
original column and delete the temporary column of data.

You can then sort the data and the numbers will be in the correct order and
having sorted them, all the numbers should be together and if you want to
remove the leading zeros then another temporary column and the formula
=VALUE(Ref) will convert them back to numeric without the leading zeros.



"mcolson" wrote:

In Excel, I've tried setting all of the data to text for that column
and then sorting it. It still sorts the values as if they were
numbers, rather than comparing the first character, then the second
character ... as it does when sorting alphabetically. This wouldn't
be such a problem if I had just numbers, or just cells that contained
a mixture of characters (never just numbers).

Matt

On Feb 7, 10:46 am, "Bob Phillips" wrote:
It is seeing it all as text.

After importing it, select the column, and do a DataText To Columns, with
Fixed Width and finish.

They should all be numbers then and sort correctly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mcolson" wrote in message

ups.com...

I have a column which i've set as a range. This range contains cells
with numbers only, and cells which contain a mixture of characters.
At the beginning, I sort the information in ascending order. I then
import data where it should go in the order. The problem I am running
into is that when I import data, it sees a number like 1234 as lower
than 5. But when I sort the information the next time, it puts the 5
in front of the 1234. If I were to add a character such as a dash
after each of the numbers. It would see 1234- as lower than 5- and
also see it as lower the next time I do a sort, which I would like it
to. Does what I'm saying make sense? Does anyone have any
suggestions?

Copied a wrong formula in the previous message. Try this one.

=IF(ISNUMBER(A1),TEXT(A1,"0000"),A1)

"OssieMac" wrote:

Somehow you need to pad the numbers with leading zeros so that all the
numbers have the same number of characters. Is it practicable within your
needs to do this? If so, you could put the following formula in a temporary
column next to the column of data to convert all the numbers only to text and
padded with leading zeros to provide a string length to whatever you want.
(Change the number of zeros between the inverted commas to alter the string
length.)
The formula will only convert the cells it can identify as numeric and
simply copy the other cells with alpha and alpha/numeric data. The formula
assumes original data is in Cell A1 and formula in cell B1.

=IF(ISNUMBER(A1),VALUE(A1),A1)

Copy the formula to the full length of the data and when you have done this,
select the column with the formulas and copy, paste special, values to remove
the formulas and leave the values then and copy and paste it over the
original column and delete the temporary column of data.

You can then sort the data and the numbers will be in the correct order and
having sorted them, all the numbers should be together and if you want to
remove the leading zeros then another temporary column and the formula
=VALUE(Ref) will convert them back to numeric without the leading zeros.



"mcolson" wrote:

In Excel, I've tried setting all of the data to text for that column
and then sorting it. It still sorts the values as if they were
numbers, rather than comparing the first character, then the second
character ... as it does when sorting alphabetically. This wouldn't
be such a problem if I had just numbers, or just cells that contained
a mixture of characters (never just numbers).

Matt

On Feb 7, 10:46 am, "Bob Phillips" wrote:
It is seeing it all as text.

After importing it, select the column, and do a DataText To Columns, with
Fixed Width and finish.

They should all be numbers then and sort correctly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mcolson" wrote in message

ups.com...

I have a column which i've set as a range. This range contains cells
with numbers only, and cells which contain a mixture of characters.
At the beginning, I sort the information in ascending order. I then
import data where it should go in the order. The problem I am running
into is that when I import data, it sees a number like 1234 as lower
than 5. But when I sort the information the next time, it puts the 5
in front of the 1234. If I were to add a character such as a dash
after each of the numbers. It would see 1234- as lower than 5- and
also see it as lower the next time I do a sort, which I would like it
to. Does what I'm saying make sense? Does anyone have any
suggestions?