Hi,
I think this is an Excel query, although I'll be linking
the table to an Access database. I've encountered a
problem regarding reference numbers (which is the primary
key in the Access database) not being in the same format.

My objective is to be able to consolidate a number of
records where the primary keys are not equal. For
example, data in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
12/101/002 Smith & Co 15/11/03 £4000

My problem is that I can't use the Left() function in
Access because of the inequalities. So I was wondering if
there's a way of identifying the length of the ref number
within Excel and then padding with zeros at the beginning
in order to use the Left() function?

BUT, there is also the issue that a reference can vary in
length from:
####/###/###??? to #######??# or ##/###/###?
where '#' indicates a number and '?' indicates a character.

Has anyone got any ideas on how this can be achieved (if
it is possible)? I hope so!

Amanda