Solved

Excel Sort

Posted on 2011-10-24
Medium Priority
280 Views
I have a column that contains data in the format:
Alpha:Numeric.Surname,Firstname

I want to sort on the Surname, Firstname. I don't care if the Alpha:Numeric. is deleted.
0
Question by:Won2Ok

LVL 43

Expert Comment

ID: 37022101
Can you give a few examples here?
0

LVL 10

Expert Comment

ID: 37022128
Your best bet is to split the data into columns, then you'll have much more control for sorting. You can do so by clicking data, text to columns.
0

LVL 4

Accepted Solution

AgeOfEmpires earned 2000 total points
ID: 37022184
If this is the exact format of your data for every row, and you prefer not to use the text to columns (which would break out your data into multiple columns), you can leave the original data undisturbed and parse out Surname,Firstname is another column and sort on that column.  To parse the data (assuming your first Alpha:Numeric.Surname,Firstname is in cell A1) would be:

=MID(A1,SEARCH(".",A1)+1,100)

Now a couple of things about the above formula - it assumes that the first occurrence of a "." is 100% the divider between Alpha:Numeric and Surname,Firstname.  If Alpha:Numeric could contain a ".", this formula will break.  Also, the formula is a little kludgy since you use an artificially high number of 100 for the number of characters to grab after the "." is found.  If this threshold fits well within your data, great.  If not, you can increase it to a suitable number.  The logically clean way would be to calculate the position of the first "." again in the third term of the MID function and subtract that from the length(A1)-1.  However, cheating with an artificially high length let's you avoid those two additional calcs.

One note about my solution and the one offered by etech0 - if "." can occur one or more times to the left of the "." separator between Alpha:Numeric and Surname,Firstname, then you have a little more work to do.

Hope this helps.
0

Author Closing Comment

ID: 37022438
Thanks
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll