number format problem when using =A1&A2 formula
 

Hello

I am trying to use the contents of 2 cells to produce I.D. numbers the first
cell contains 2 or 3 letters the second cell is a counter which advances
each time a macro is run.
I have no problem joining the 2 together by using =A1&A2 (or even
=A1&""&A2).
The problem is I am compiling a database of several hundred of these records
and eventually I sort the records and the results are not in the correct
order.
If these had just been numbers I would use 0001, 00002 etc by formatting but
this is not working when the 2 cells are joined.
Even though A1 entry is CH and A2 is formatted and reads 0001 the result is
CH1 - needless to say sort would put CH11 before CH9
I tried putting some zeros in by =A1&"000"&A2 but this did not work as it
was messed up as soon as A2 contained double figures!

Can anyone suggest how I can get the results I want?

Kenny
Win2000 and Excel97 (forced upon me-not my choice!!!)

try = A1 & text(A2,"0000") if you want 4 digits
if A1 can also be a number and A2 can be text also
=if(isnumber(A1),text(A1,"0000"),A1) & if(isnumber(A2),text((A2,"0000"),A2)

"N E Body" wrote:

Hello

I am trying to use the contents of 2 cells to produce I.D. numbers the first
cell contains 2 or 3 letters the second cell is a counter which advances
each time a macro is run.
I have no problem joining the 2 together by using =A1&A2 (or even
=A1&""&A2).
The problem is I am compiling a database of several hundred of these records
and eventually I sort the records and the results are not in the correct
order.
If these had just been numbers I would use 0001, 00002 etc by formatting but
this is not working when the 2 cells are joined.
Even though A1 entry is CH and A2 is formatted and reads 0001 the result is
CH1 - needless to say sort would put CH11 before CH9
I tried putting some zeros in by =A1&"000"&A2 but this did not work as it
was messed up as soon as A2 contained double figures!

Can anyone suggest how I can get the results I want?

Kenny
Win2000 and Excel97 (forced upon me-not my choice!!!)

Hi,

Try this

=A1&TEXT(A2,"0000")

Regards
Paul

"N E Body" wrote in message
...
Hello

I am trying to use the contents of 2 cells to produce I.D. numbers the
first
cell contains 2 or 3 letters the second cell is a counter which advances
each time a macro is run.
I have no problem joining the 2 together by using =A1&A2 (or even
=A1&""&A2).
The problem is I am compiling a database of several hundred of these
records
and eventually I sort the records and the results are not in the correct
order.
If these had just been numbers I would use 0001, 00002 etc by formatting
but
this is not working when the 2 cells are joined.
Even though A1 entry is CH and A2 is formatted and reads 0001 the result
is
CH1 - needless to say sort would put CH11 before CH9
I tried putting some zeros in by =A1&"000"&A2 but this did not work as it
was messed up as soon as A2 contained double figures!

Can anyone suggest how I can get the results I want?

Kenny
Win2000 and Excel97 (forced upon me-not my choice!!!)

Many Thanks Paulw2k and bj

Works a treat.
I tried half a day and failed to get it! You have allowed me to have a
peaceful nights sleep!!!

Kenny


"Paulw2k" wrote in message
...
Hi,

Try this

=A1&TEXT(A2,"0000")

Regards
Paul

"N E Body" wrote in message
...
Hello

I am trying to use the contents of 2 cells to produce I.D. numbers the
first
cell contains 2 or 3 letters the second cell is a counter which advances
each time a macro is run.
I have no problem joining the 2 together by using =A1&A2 (or even
=A1&""&A2).
The problem is I am compiling a database of several hundred of these
records
and eventually I sort the records and the results are not in the correct
order.
If these had just been numbers I would use 0001, 00002 etc by formatting
but
this is not working when the 2 cells are joined.
Even though A1 entry is CH and A2 is formatted and reads 0001 the result
is
CH1 - needless to say sort would put CH11 before CH9
I tried putting some zeros in by =A1&"000"&A2 but this did not work as
it
was messed up as soon as A2 contained double figures!

Can anyone suggest how I can get the results I want?

Kenny
Win2000 and Excel97 (forced upon me-not my choice!!!)