Excel Sort

Posted on 2011-10-24
Last Modified: 2013-11-05
I have a column that contains data in the format:

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

    Expert Comment

    by:Saqib Husain, Syed
    Can you give a few examples here?
    LVL 10

    Expert Comment

    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.
    LVL 4

    Accepted Solution

    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:


    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.

    Author Closing Comment


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now