LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Sorting a column with Text and Numbers

But if the data was always consistent -- variable text followed by a space
character, then numbers, you could use a formula to extract that last portion
and sort by that helper column...

One way:
=--trim(right(substitute(trim(a1)," ",rept(" ",99)),99))

It replaces the space character with 99 space characters and then looks at the
rightmost 99 characters. Trims that and the -- changes the text back to
numbers.

If your data isn't always laid out this way, you'll want to share more samples.

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
 
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 column of numbers joeydusmc Excel Discussion (Misc queries) 1 October 14th 08 09:49 PM
Help sorting text as numbers [email protected] Excel Worksheet Functions 1 November 13th 07 03:34 AM
Sorting (Numbers with Text) JSpence2003 Excel Discussion (Misc queries) 8 August 31st 07 10:30 PM
sorting a column with numbers and text Ford35 Excel Worksheet Functions 5 September 29th 06 05:02 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 03:46 PM.

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

About Us

"It's about Microsoft Excel"