Hi,

I have several hundred entries in an Excel spreadsheet that I need to sort alphabetically from A-Z. Sounds easy eh, but...

The problem is that EVERY entry starts with 8 numerical digits, 3 alphabet characters and an underscore followed by a string of letters.

eg: 20130201ABC_jackson_bob

It is the string after the first underscore that I need Excel to sort from A-Z not the number before it as it tries to do!

Is there a way to customise the cells so that it ignores the first 12 characters or or do I need to write a Macro (VBA) to do this

Tnx for any help you may have.

 

Neither. Use a formula in a parallel column to parse out the part you want to sort on and then sort both columns on the contents of the parallel column. Since the first character you want to sort on is always the 13th character, this will be easy using the MID() function.

 

Neither. Use a formula in a parallel column to parse out the part you want to sort on and then sort both columns on the contents of the parallel column. Since the first character you want to sort on is always the 13th character, this will be easy using the MID() function.

Thanks. That worked and it was easier than I thought. A little help goes a long way.

 

Excel (spreadsheets in general) are amazingly powerful tools if you are willing to spend some time getting familiar with the functions that are available combined with learning how to look at problems from a somewhat different perspective. Seldom do you need to resort to VBA or some equivalent unless you want to get really fancy or make a GUI or control an instrument or something like that.

 

Excel (spreadsheets in general) are amazingly powerful tools if you are willing to spend some time getting familiar with the functions that are available

Yes, it pays to check them from time to time.

The last I seen used is conditional formating that helps a lot in visualizing ranges amongst results. Very effective. Have to apply that myself at any moment.