Sorting formatted numbers

[Hunter]

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.

 

[B. R.Ramachandran]

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

 

[Dave Peterson]

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 ???