Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
confused on the tundra
 
Posts: n/a
Default Sorting when some numbers have a text suffix

I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with a
coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are
six digits long if that helps i.e. 123456A. Now when I sort the numbers sort
fine but all the numbers with a letter attached are at the bottom of the
list, presumably as text. How can get the numbers and text"numbers" to sort
together?
  #2   Report Post  
CLR
 
Posts: n/a
Default

One way would be to break off the letters using data Text-to-columns
Fixed, and put them in the next column, then sort using your regular column
as the first sort-key and this new column as your second
sort-key............then you could Concatenate them back together if you
wish......

Vaya con Dios,
Chuck, CABGx3




"confused on the tundra"
wrote in message ...
I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with

a
coulpe that have "B", "C", "D", and "E" . All the numbers with a letter

are
six digits long if that helps i.e. 123456A. Now when I sort the numbers

sort
fine but all the numbers with a letter attached are at the bottom of the
list, presumably as text. How can get the numbers and text"numbers" to

sort
together?



  #3   Report Post  
Domenic
 
Posts: n/a
Default

Try the following...

C2, copied down:

=IF(D2="",B2,--SUBSTITUTE(B2,D2,""))

D2, copied down:

=IF(ISNUMBER(--B2),"",RIGHT(B2))

Then select Columns B, C and D, and sort by Column C and D...

Data Sort Sort by "Column C" Then by "Column D"

Hope this helps!

In article ,
"CLR" wrote:

One way would be to break off the letters using data Text-to-columns
Fixed, and put them in the next column, then sort using your regular column
as the first sort-key and this new column as your second
sort-key............then you could Concatenate them back together if you
wish......

Vaya con Dios,
Chuck, CABGx3




"confused on the tundra"
wrote in message ...
I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with

a
coulpe that have "B", "C", "D", and "E" . All the numbers with a letter

are
six digits long if that helps i.e. 123456A. Now when I sort the numbers

sort
fine but all the numbers with a letter attached are at the bottom of the
list, presumably as text. How can get the numbers and text"numbers" to

sort
together?

  #4   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

Create an helper column with formula in it (an example for row a2 below):
="" & B2
and sort your data on this helper column.


Arvi Laanemets


"confused on the tundra"
wrote in message ...
I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with

a
coulpe that have "B", "C", "D", and "E" . All the numbers with a letter

are
six digits long if that helps i.e. 123456A. Now when I sort the numbers

sort
fine but all the numbers with a letter attached are at the bottom of the
list, presumably as text. How can get the numbers and text"numbers" to

sort
together?



  #5   Report Post  
confused on the tundra
 
Posts: n/a
Default



"Domenic" wrote:

Try the following...

C2, copied down:

=IF(D2="",B2,--SUBSTITUTE(B2,D2,""))


This populated column C with values from B until it got to the numbers with
the letter suffix and put #value with the little triangle in the upperleft
corner.

D2, copied down:

=IF(ISNUMBER(--B2),"",RIGHT(B2))


When I do this no numbers show up in the any of the cells but the formula is
in the Fx box on the tool bar for all the cells.

Then select Columns B, C and D, and sort by Column C and D...

Data Sort Sort by "Column C" Then by "Column D"


When I sort it stays the same as before with the number with suffixes on the
bottom.


What am I doing wrong?


One way would be to break off the letters using data Text-to-columns
Fixed, and put them in the next column, then sort using your regular column
as the first sort-key and this new column as your second
sort-key............then you could Concatenate them back together if you
wish......

BINGO! that did the trick. Thanks
Vaya con Dios,
Chuck, CABGx3




  #6   Report Post  
confused on the tundra
 
Posts: n/a
Default

That got it! Thanks. I just have to remenber to keep the two pre concatenate
columns there so I can resort after changes and sort by other columns.

"CLR" wrote:

One way would be to break off the letters using data Text-to-columns
Fixed, and put them in the next column, then sort using your regular column
as the first sort-key and this new column as your second
sort-key............then you could Concatenate them back together if you
wish......

Vaya con Dios,
Chuck, CABGx3




"confused on the tundra"
wrote in message ...
I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with

a
coulpe that have "B", "C", "D", and "E" . All the numbers with a letter

are
six digits long if that helps i.e. 123456A. Now when I sort the numbers

sort
fine but all the numbers with a letter attached are at the bottom of the
list, presumably as text. How can get the numbers and text"numbers" to

sort
together?




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
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM
numbers and text in Excel to read as text keeping the leading zer. Ralph Excel Discussion (Misc queries) 2 December 10th 04 08:05 PM
I enter numbers and they are stored as text burkeville Excel Discussion (Misc queries) 5 December 3rd 04 02:59 AM
Converting text to numbers Scott Excel Discussion (Misc queries) 3 November 26th 04 10:17 PM


All times are GMT +1. The time now is 11:26 AM.

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"