Our back office system at work has order numbers prefixed with letters - PORD for purchase - SORD for sales

The orders have just turned over a million and in excel when sorting either column the newer orders are appearing first

Any way to make Excel automatically realise that SORD1122152 should come AFTER SORD964533

rather than it getting as far as deciding 1 is before 9 and sorting accordingly?

create 2 new cols (B&C):

colA = PORDxxxxx or SORDxxxxxxx

colB = left(colA, 4)

colC = right(colA, (len(colA)-4))

Or if you fancied, write a function to break the two apart, pad the order number up to a million (with leading zeros) and reassemble to give PORD0000001, SORD0000002, etc.

I can knock the code up a few a seconds if you like.

hmmm i was hoping it'd be a setting change (like using english date formatting rather than American)

given this report is run everyday and the problem will be over when all the 900,000 orders are done i think i'll put up with it until then

actually i think the report has a date column too so can add that as an extra sort field

Does the report generate a new excel file each day, or is it based on a template? Could you not just process the report after generation automatically? Not knowing quite how things work in your environment, I am only guess.

Anyway, here is a quick and dirty function to do the job:

Public Function padit(strOrder As String, intLength As Integer) As String
'
'
'

Dim strNumber As String

   'Get the number
   strNumber = Right(strOrder, (Len(strOrder) - 4))

   'Pad it out to the required length
   While Len(strNumber) < intLength
       strNumber = "0" & strNumber
   Wend

   'Return padded out string
   padit = Left(strOrder, 4) & strNumber

End Function

Edited 1 December, 201114 yr by mbames

Is it possible for you to send the 'said report' or does it contain sensitive information?

I would be happy to do the function for you if you like. Mbames is using the right idea by splitting the two up and then calling both column A and then B later -

Our back office system at work has order numbers prefixed with letters - PORD for purchase - SORD for sales

The orders have just turned over a million and in excel when sorting either column the newer orders are appearing first

Any way to make Excel automatically realise that SORD1122152 should come AFTER SORD964533

rather than it getting as far as deciding 1 is before 9 and sorting accordingly?

Its doing it because excel is reading them as strings so reading them alphabetically, so anything that starts SORD1 will be before SORD9 (the way to have stop this would have to be put a "0" in front of the order number < 1m ie. your example above would read SORD0964533.)

I know this doesn't help much but explains why it is happening.

Edited 1 December, 201114 yr by io1901

You can reverse sort, but it escapes me ATM how to do it.

Might sound daft but can the new order system post SORD999999 be put into a separate excel document or tab?