cant get entire column to format the same

[junedonofrio]

i am trying to sort three columns, first column has numbers other two columns have text. i have entered all the info & now i cant get the first column with the numbers to sort, i have tried changing the format from number to general to text, but for some reason only some of the entered numbers format others stay the same as entered, if i go & enter the numbers over again they change to the new format, i really dont want to have to re-enter all the stuff i did. i have re-started my computer, just cant figure this out !! any help will be greatly appreciated.
thanks,
June

 

[SkipVought]

junedonofrio,

Sounds like some of your 'numbers' are TEXT values. How did you enter this data? What are some examplles of 'numbers' that don't format as numbers?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[waynerenaud]

Hi junedonofrio, try this (Straight from excel help)

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.

 

[junedonofrio]

thanks so much, i tried the excel help solution of "copy, paste special". i dont know why it worked but it did. i am finally able to get the numbers all formated the same & able to sort. thanks so much !!

June

 

[xlbo]

It is because some of the "numbers" were actually text strings that look like numbers. Formatting cannot CHANGE the contents of a cell - it only affects what it LOOKS like.

What you are doing by using the pastespecial method is multiplying every value by 1 - thus forcing excel to convert them to true numbers - once this is done, the formatting can be set

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[junedonofrio]

thanks for the explaination, the thing i really don't understand is why the numbers came out different to begin with, must have something to do with the way i entered, but i dont think i did anything different, except maybe sometimes i used the numeric key pad, & sometimes the keyboard numbers, will that make a difference ??? which should i be useing the numeric keypad ??

thanks,
June

 

[xlbo]

shouldn't make a difference. The only way I can replicate this is if I format a block of cells as text BEFORE entering the data. If you enter a number into a text formatted cell, it becomes text that LOOKS like a number. I would wager that this is what has happened here.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[junedonofrio]

thanks Geoff, so much for the info.

June