Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting: Sorting by the First Character
I'm looking to sort my data by looking at the first character only. Here's an
example data set: 21 100 4020 56 32 I want this data set to look like this: 100 21 32 4020 56 I think it may require some programming, but I'm not sure. And I haven't the slightest idea on how to program. Can anyone help shed some light on my issue? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting: Sorting by the First Character
Use a 'helper' column and sort on that, say in D1, enter...(when your number
is in A1) =LEFT(A1,1) sort now on column D...(Not sure if this will make thinks unique enough or not as you may need to use another column too for the second sort level. If you need further numbers stripped from the data in other helper columns use the MID function, like so (2nd character) =MID(A1,2,1) You may also find that formatting these numbers as text will work, but you will not then be able to apply numeric functions to them (SUM, AVERAGE, Etc) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "dzuy" wrote in message ... I'm looking to sort my data by looking at the first character only. Here's an example data set: 21 100 4020 56 32 I want this data set to look like this: 100 21 32 4020 56 I think it may require some programming, but I'm not sure. And I haven't the slightest idea on how to program. Can anyone help shed some light on my issue? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting: Sorting by the First Character
Insert a helper column to the right of the number and in the first row enter
the following formula: =VALUE(LEFT(A1,1)) Then copy the formula down the column and sort your data on the helper column results. After the sort you can delete the helper column The Left function extracts the first character of the value, and the value function converts it back to a number so you can sort it numerically. -- Kevin Backmann "dzuy" wrote: I'm looking to sort my data by looking at the first character only. Here's an example data set: 21 100 4020 56 32 I want this data set to look like this: 100 21 32 4020 56 I think it may require some programming, but I'm not sure. And I haven't the slightest idea on how to program. Can anyone help shed some light on my issue? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Sorting Sorting | Excel Discussion (Misc queries) | |||
Odd! Excel changes my letter character. Why? | Excel Discussion (Misc queries) | |||
Count occurrence of character within a cell | Excel Discussion (Misc queries) | |||
Excel and access should permit sorting by last character | Excel Worksheet Functions | |||
Copy a column in worksheet with a character change | Excel Worksheet Functions |