If anyone can help me solve this rather annoying problem in Excel I would be most grateful.
I have a list that when sorted in order sorts out figures in this format:
A1
A10
A100
A2

so I need a macro code of some description to reverse the auto alpha prefix and numeric suffix to sort the figures in this order:
A1
A2
A10
A100

if anyone knows or has the code to hand I would be very grateful.

Thanks for any help, it will be much appreicated!

Caroline

If you can modify your data, then there are two ways around this.  Your data's "type" is alphanumeric as it stands (ie text) and that is why it is (correctly) sorting as you described.  It is not an alpha prefix and numeric suffix.  So, you can either

a)  split the two bits of information into two columns, one alpha for the prefix and the other numeric for the suffix and then sort, or
b)  put leading zeroes in front of the numeric part of your data so that the text string has a more appropriate ASCII value.

eg
A0001
A0002
A0010
A0100

Hope this helps and isn't too much work for you!

Thanks for the advice.  I have for speed split the alpha and numbers into 2 separate columns and has worked great (very logical thing to do and completely passed me by!)

As I have a lot more data to convert to this type I was hoping that there would be a way to do it by macro for speed (unfortunately due to reasons I won't bore you with I am unable to use your second point of adding the 0)

Again, thanks for your help.  Immediate problem solved!

Caroline

Hi Caroline,

Glad to be of help!  Means my grey matter still has a use sometimes!

I've never bothered with excel macros so can't do this for you, but if you have the time it should be possible to write one to do what you want.  This is what it needs to do (in English, not excel!) :

1.  Read the text string from the first column.
2.  Take the first character off and store it in a second column.
3.  Take the remaining characters and store them in a third column.

I expect there'll be commands such as SUBSTR or similar for parsing strings.  Then make sure the columns are set to the right datatypes before sorting.

Hi again Caroline,

Try using these Text Functions - LEFT, RIGHT, LEN.  So if you are looking at cell A2:

LEFT(A2, 1) will take the first character from the left
RIGHT(A2, LEN(A2) - 1) will take the length-of-the-string characters minus 1 from the right, thereby ignoring the first character.

Hope this helps with the macro! :)

I think this may well help - I'll let you know if it does!
It's kind of you to take time out to help.
Thanks
Caroline