Weird Problem Sorting in Excel. 3

[TIGREBLANCO]

Hi everyone

This is not the first time that I see Excel sorting a list of values in the wrong way.
I'm sure it has to do with cell formatting or something like that cause if I copy an existing cell's value and paste it to the end of the list it will get sorted properly whereas if I directly type the same exact value in a new cell it will attain position #1 in the list (beginning of list) after sorting and not among cells with corresponding values.
Does anyone know what causes this behavior and how to prevent/correct it? I've looked for answers related to this issue but there's nothing, so any help will be appreciated.

Thanks
Sal.

 

[KenWright]

Your data is not what it seems. Either you think it is text and it is in fact numeric, or vice versa. Retyping a 'text' number will cause it to become numeric, and as such when sorted it will not line up with other 'numbers' that look to be the same.

One way to fix this is if the data is meant to be numeric is to copy an empty cell, select all your data and do Edit / Paste Special / Add, which will coerce all text numbers to actual numeric format. You cannot just format them and have them bacome numeric, whatever it may say in the Format dialog box.

Regards
Ken.................

 

[TIGREBLANCO]

Thanks!!
Solution its simple, but I would have spend a lot of time researching for it.
Your solution fits perfectly in my situation since the steps you mentioned can be used programaticaly. I'm giving you a star for all the time you saved me. Thanks!

 

[ladyck3]

KenWright gets ONE DELUXE STAR!

Way cool... I always put a "1" in a blank cell and then copy/paste special and choose MUTIPLY...

Your version to simply copy a blank cell and paste special chosing Add is even faster and easier to explain to someone over the phone.... WOO HOO!

Thanks Ken

 

[xlbo]

comin up with some right little treats lately Ken - very nice addition to the *1 solution

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[KenWright]

Why thanky'all kindly.

Regards
Ken.............