Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Sorting a column that contains both text and numbers
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Sorting a column that contains both text and numbers
I'm trying to sort a column that contains both text and numbers so that the
numbers appear in order instead of 1,10,11,12,19,20,21, etc. I can't use
text-to-columns because the text isn't uniform. When I sort, here's what i
currently get:
Computer Flex 1
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 2
Computer Flex 20
Computer Flex 21
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
FOOD & COOKING Flex 1
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
FOOD & COOKING Flex 2


and here's what I need:

Computer Flex 1
Computer Flex 2
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 20
Computer Flex 21
FOOD & COOKING Flex 1
FOOD & COOKING Flex 2
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,240
Default Sorting a column that contains both text and numbers
tstobb wrote:
I'm trying to sort a column that contains both text and numbers so that the
numbers appear in order instead of 1,10,11,12,19,20,21, etc. I can't use
text-to-columns because the text isn't uniform. When I sort, here's what i
currently get:
Computer Flex 1
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 2
Computer Flex 20
Computer Flex 21
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
FOOD & COOKING Flex 1
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
FOOD & COOKING Flex 2


and here's what I need:

Computer Flex 1
Computer Flex 2
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 20
Computer Flex 21
FOOD & COOKING Flex 1
FOOD & COOKING Flex 2
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19


Assuming the text always ends with a number, create a helper column and use that
for the sort:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99))&
" "&TEXT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99)),"0000")
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Sorting a column that contains both text and numbers
This formula gave me a column where each line reads "Overall Rank"

"Glenn" wrote:

tstobb wrote:
I'm trying to sort a column that contains both text and numbers so that the
numbers appear in order instead of 1,10,11,12,19,20,21, etc. I can't use
text-to-columns because the text isn't uniform. When I sort, here's what i
currently get:
Computer Flex 1
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 2
Computer Flex 20
Computer Flex 21
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
FOOD & COOKING Flex 1
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
FOOD & COOKING Flex 2


and here's what I need:

Computer Flex 1
Computer Flex 2
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 20
Computer Flex 21
FOOD & COOKING Flex 1
FOOD & COOKING Flex 2
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19


Assuming the text always ends with a number, create a helper column and use that
for the sort:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99))&
" "&TEXT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99)),"0000")
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Sorting a column that contains both text and numbers
My error - forgot to change the "A1" to my data cell. Changed that & it
worked perfectly - Thanks!

"Glenn" wrote:

tstobb wrote:
I'm trying to sort a column that contains both text and numbers so that the
numbers appear in order instead of 1,10,11,12,19,20,21, etc. I can't use
text-to-columns because the text isn't uniform. When I sort, here's what i
currently get:
Computer Flex 1
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 2
Computer Flex 20
Computer Flex 21
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
FOOD & COOKING Flex 1
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
FOOD & COOKING Flex 2


and here's what I need:

Computer Flex 1
Computer Flex 2
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 20
Computer Flex 21
FOOD & COOKING Flex 1
FOOD & COOKING Flex 2
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19


Assuming the text always ends with a number, create a helper column and use that
for the sort:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99))&
" "&TEXT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),99)),"0000")
.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default Sorting a column that contains both text and numbers
check your other post.

tstobb wrote:

I'm trying to sort a column that contains both text and numbers so that the
numbers appear in order instead of 1,10,11,12,19,20,21, etc. I can't use
text-to-columns because the text isn't uniform. When I sort, here's what i
currently get:
Computer Flex 1
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 2
Computer Flex 20
Computer Flex 21
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
FOOD & COOKING Flex 1
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19
FOOD & COOKING Flex 2

and here's what I need:

Computer Flex 1
Computer Flex 2
Computer Flex 3
Computer Flex 4
Computer Flex 5
Computer Flex 6
Computer Flex 10
Computer Flex 11
Computer Flex 12
Computer Flex 13
Computer Flex 14
Computer Flex 15
Computer Flex 16
Computer Flex 17
Computer Flex 18
Computer Flex 19
Computer Flex 20
Computer Flex 21
FOOD & COOKING Flex 1
FOOD & COOKING Flex 2
FOOD & COOKING Flex 10
FOOD & COOKING Flex 11
FOOD & COOKING Flex 12
FOOD & COOKING Flex 13
FOOD & COOKING Flex 14
FOOD & COOKING Flex 15
FOOD & COOKING Flex 16
FOOD & COOKING Flex 17
FOOD & COOKING Flex 18
FOOD & COOKING Flex 19

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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 a column with Text and Numbers tstobb Excel Discussion (Misc queries) 1 November 11th 09 10:46 PM
sorting column of numbers joeydusmc Excel Discussion (Misc queries) 1 October 14th 08 09:49 PM
sorting a column with numbers and text Ford35 Excel Worksheet Functions 5 September 29th 06 05:02 PM
SORTING TEXT AND NUMBERS jstephenson Excel Worksheet Functions 5 February 10th 06 08:44 PM
sorting numbers in a column kailuamike New Users to Excel 2 December 19th 05 02:58 AM


All times are GMT +1. The time now is 12:10 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"

 

Contact Us ExcelBanter forum home Privacy Statement