Sorting a column that contains both text and numbers

T

tstobb

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
 
G

Glenn

tstobb said:
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
Click to expand...


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")
 
T

tstobb

My error - forgot to change the "A1" to my data cell. Changed that & it
worked perfectly - Thanks!
 
D

Dave Peterson

check your other post.
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting a column with Text and Numbers 1
Finding the weighted average of text that have been given values 1
Problem formatting dates from CSV file 2
Crosstab vs Flex Grid 17
How to getting text labels to show up in scatter chart. 0
How do I have excel highlight days before & after a date on a cale 2
15 minute data to daily data 1
time_report_date-wise_employee-wise 6

Top