Hello everybody,

I have a table with codes (6digits). Some of them start with a zero.
I formatted the cell with a custom format: 000000, which works fine.
Instead of 41719, I can see 041719

In a certain cell I concatenate the entries of two cells, the above
mentioned code and another code.

=concatenate(code1," / ",code2)

637002 9588 becomes 637002 / 9588

but

041719 9580 becomes 41719 / 9580

I lost the zero in front and I have no idea how I can get it back. I
tried various possibilities with cell formats.

The idea of formatting the code1 table as text and write '041719, I
don't like, because in case of sorting the table, these entries will
be at the end instead at the top.

Thanks,
Norbert

=TEXT(code1,"000000")&" / "&Text(code2,"000000")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Orion" wrote in message
...
Hello everybody,

I have a table with codes (6digits). Some of them start with a zero.
I formatted the cell with a custom format: 000000, which works fine.
Instead of 41719, I can see 041719

In a certain cell I concatenate the entries of two cells, the above
mentioned code and another code.

=concatenate(code1," / ",code2)

637002 9588 becomes 637002 / 9588

but

041719 9580 becomes 41719 / 9580

I lost the zero in front and I have no idea how I can get it back. I
tried various possibilities with cell formats.

The idea of formatting the code1 table as text and write '041719, I
don't like, because in case of sorting the table, these entries will
be at the end instead at the top.

Thanks,
Norbert

One way:

=TEXT(code1,"000000\/") & TEXT(code2,"0000")

In article ,
Orion wrote:

Hello everybody,

I have a table with codes (6digits). Some of them start with a zero.
I formatted the cell with a custom format: 000000, which works fine.
Instead of 41719, I can see 041719

In a certain cell I concatenate the entries of two cells, the above
mentioned code and another code.

=concatenate(code1," / ",code2)

637002 9588 becomes 637002 / 9588

but

041719 9580 becomes 41719 / 9580

I lost the zero in front and I have no idea how I can get it back. I
tried various possibilities with cell formats.

The idea of formatting the code1 table as text and write '041719, I
don't like, because in case of sorting the table, these entries will
be at the end instead at the top.

Thanks,
Norbert

Change your formula as under:
=CONCATENATE(TEXT(code1,"000000")," / ",code2)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Wow, that seemed to be a very easy task for the specialists!!

Thanks to Sharad, JE McGimpsey and Bob Phillips for that superquick
response.

Norbert


On Tue, 21 Dec 2004 04:27:10 -0800, Sharad
wrote:


Change your formula as under:
=CONCATENATE(TEXT(code1,"000000")," / ",code2)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It's been asked a few times Norbert, so we had the answer tucked up our
collective sleeve :-)

Bob


"Orion" wrote in message
...
Wow, that seemed to be a very easy task for the specialists!!

Thanks to Sharad, JE McGimpsey and Bob Phillips for that superquick
response.

Norbert


On Tue, 21 Dec 2004 04:27:10 -0800, Sharad
wrote:


Change your formula as under:
=CONCATENATE(TEXT(code1,"000000")," / ",code2)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!