data sort not recognizing

[Boutspers]

I seem to be having a recurring problem with sorting numerical data that is being treated as text. What I'm working with is 6-digit strings, formatted as text. The string is all numbers, but must be formatted as text because some strings begin with "0".

What happens is that we start with an initial column of say, 300 cells. We then paste in another 100 cells below, which then need to be sorted into the original 300. The entire column is highlighted and formatted to text.

Upon sorting, the 100 new cells sort at the top, in order relative each other, then the original 300 are sorted below the new 100, again in relative order. I find with the 100 new cells, if I start retyping them manually and resort, the retyped ones will sort properly into the larger list.

Obviously somehow the formatting is not being recognized. All the data is structured exactly the same - meaning there are no single quotes or characters before the 6 digits. All I need to do is be able to paste new cells into the column and have them sort properly. I would think that formatting all the cells the same (as text) would create uniformity. It doesn't. How can I easily remedy this?

Remember, some strings start with a "0", so I *cannot* convert this cells to numerical format, or I'll lose an important digit. I must have these formatted as text or something equivolent.

Thanks so much for helping.

 

[Powergrip]

Turn the cells into numercial format. If the number starts with a "0" put a ' in front of the 0. It will keep the 0 in front and you get to have it as a number. You might not be able to see what I said so here it is again, put a ' in front. The key is the non shifted " key. Hope this helps.

 

[Boutspers]

I don't know of a fast way to insert a ' in front of all the hundreds of strings that begin with "0". Is there an easy way to do that?

 

[Boutspers]

The above suggestion won't work anyway. I need to keep these cells formatted as text, ultimately. I can't have them be treated as numbers because they are not numbers. They are SKU numbers.

 

[Boutspers]

This is seriously getting very frustrating. I've copied a few cells to different worksheets just to mess around with the sorting. I have four cells in a column:

211058
211059
010313
010414

All four cells have identical formatting and there are no special characters besides the digits shown. They are formatted as text, and yet every autosort produces the same order shown above. Yet if I simply retype the cell "211058" and press enter, a data sort on the column will then produce the following:

211059
010313
010414
211058

How can this be happening?

 

[Seti]

Try doing a Data|Text to Columns on the column and make sure they are set to Text, not General.

 

[Boutspers]

YEAH!!! I was not aware of that command. That did the trick, although I'm still not sure why. Thank you so much for helping me get past this obstacle.