HELP! How to Use Concatenate Formula with Leading Zeros??

[LionBear]

Hi All - I hope someone can help me with this!

I am using the following formula: =CONCATENATE($Page.$B$6;$'Data'.C9;$Page.$B$7)

In the 'Data' range, i have some values with leading zeros. When using the concatenate formula, these leading zeros do not follow through. For example, I get the following:

http://www.site.com/cat/prod/part=70/501

when I want this (note the two leading zeros in 0070):

http://www.site.com/cat/prod/part=0070/501

How can I make this work?

 

[Scott Huish]

Your page references are strange. Are you using Excel or Open Office?


In General though,

if you have a 4 digit number that may include leading zeros, put it in the TEXT function

i.e.

=TEXT(A1,"0000")

 

[LionBear]

Yes, I am using open office. Unfortunately I don't have access to excel

The 'Page" and 'Data' portions of the formula are referencing different sheets that contain data. How would I include the TEXT part of the formula in my current formula?

 

[Scott Huish]

Which part of the formula returns the 70?

 

[LionBear]

The 'Data' portion.

 

[Scott Huish]

=CONCATENATE($Page.$B$6;TEXT($'Data'.C9;"0000");$Page.$B$7)

Not sure of Open Office syntax, but something like that.

 

[LionBear]

Unfortunately that didn't work either. No worries, I went another route. Thanks anyway!