Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hunter
 
Posts: n/a
Default 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   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting numbers and text separately Tim C Excel Discussion (Misc queries) 8 July 21st 05 12:53 AM
Converting numbers formatted as text to numbers Bill Excel Discussion (Misc queries) 1 July 19th 05 07:10 PM
Sorting alphanumeric numbers maurice.centner Excel Discussion (Misc queries) 2 May 6th 05 02:00 AM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM


All times are GMT +1. The time now is 09:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"