Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting formatted numbers
My number in the cell is 1111111111 I add custom format to 111-111-1111.
I need to copy and paste to this same column numbers that are already formatted as 111-111-1111. When I sort the custom formated numbers fall first because its sorting by the data range 1111111111. How can I sort both kinds of data together. |
#2
|
|||
|
|||
Hi,
Even after custom formatting as ###-###-###, the data are still numeric, and therefore they will retain their values when sorted. So, after custom formatting the numbers, select the area containing those cells, Copy, and go to 'Paste Specical' (in Edit), select 'Values' under the section 'Paste' , and click 'OK' Now you can copy and paste to this column, other values which are already formatted (which I presume are strings and not numeric data). Now you should be able to sort these data the way you want. Regards, B. R. Ramachandran "Hunter" wrote: My number in the cell is 1111111111 I add custom format to 111-111-1111. I need to copy and paste to this same column numbers that are already formatted as 111-111-1111. When I sort the custom formated numbers fall first because its sorting by the data range 1111111111. How can I sort both kinds of data together. |
#3
|
|||
|
|||
It kind of sounds like you have a mixture of numbers (nicely formatted, but
still numbers) and text (that look like numbers with that nice format). If that's the case, you could convert one to the other. To convert the 111-111-1111 (really text) values to real numbers: select that range (whole column) edit|replace what: - (hyphen) with: (leave blank) replace all and give the resulting numbers that nice format. If you want to convert the numbers (111-111-1111) to text, you could insert a helper column and use a formula: =if(isnumber(c1),text(c1,"000-00-0000"),c1) Then drag this down the column. select that column edit|copy edit|paste special|values and delete the original column ??? Hunter wrote: My number in the cell is 1111111111 I add custom format to 111-111-1111. I need to copy and paste to this same column numbers that are already formatted as 111-111-1111. When I sort the custom formated numbers fall first because its sorting by the data range 1111111111. How can I sort both kinds of data together. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting numbers and text separately | Excel Discussion (Misc queries) | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |