vlookup issues

[Dave]

Excel 2003

My really be a "data" issues not a vlookup issue - but here is the problem;

1st column of my table_array:

149255
655122082938
655122082921
10655122525787
10655122525770
10655122082931

These will NOT sort in proper order.
Does not matter if the cell is formatted text.
Does not matter if the cell is formatted Number
Does not matter if I clear all the data and type them in proper order by
hand.

why won't my data sort properly?

Any help here will be appreciated.

Thanks in advance


dave

 

[Peo Sjoblom]

Formatting text to numbers won't change anything, you need to force
the text to number. First make sure you don't have trailing spaces.
Then format a cell as general, copy it, select all the data and do
edit>paste special and select add.
Now they should sort as numbers


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Perhaps Excel thinks they are text values, and changing the format
will not change the value. Ensure that the cells are formatted as
Gneral or Number, highlight the column with the values in, click on
Data | Text-to-columns, and then click on Finish.

Does that help?

Pete

 

[Shane Devenshire]

Hi Dave,

If the order you are showing us is what you get after sorting:
If the numbers are left aligned as shown below then they are probably text.
In 2003 check to see if there are error tags in the top left corner of the
cells - these could indicate the numbers are stored as text. If that is the
case highlight the list and put your mouse over the smart tag (usually to
the left of the first active cell), when the drop down arrow appears click
it and choose Convert to numbers.

Cheers,
Shane Devenshire

 

[Dave]

Thanks for the replies.
For the "temp" fix I had just let Excel sort the way it wanted and then made
my Table_array in that order..

This may be on going so your suggestions are greatly appreciated.

Thanks to all

dave