Problem with leading zero's in creating a full path
 

I need to format a series of numbers that are of different lengths to have
leading zeros to be 7 digits. I need these to be able to be merged with 2
other cells to create a field that is a path to photos, to use in a
Publisher merge.
So in cell A1, I have the first part of the path: C:\Documents and
Settings\My Documents\My Pictures\F.
In cell B2 I have the numbers (different numbers like 9, 40, 123, 4567, and
34567) that need to go after the path.
Then I have in cell C2 I have .jpg.
If I use format, custom, 0000000 it works until I merge the cells together.
When they are merged I get C:\Documents and Settings\My Documents\My
Pictures\F40.jpg. I need it to be C:\Documents and Settings\My Documents\My
Pictures\F0000040.jpg

Problem with leading zero's in creating a full path
 

If you need leading zeros in your data, then you should store the data as
Text. Cell Formatting only affects the way that data appears in a cell. It
does not change the data stored in the cell. Numbers will never contain
leading zeroes, even if the formatting makes them appear as though they do.

Change your cell formatting to Text, then re-enter your data. Or, just
re-enter using a leading apostrophe. That tells Excel to override the
existing format and treat the following data as Text.

Another option would be to use the TEXT function in your merge cell.
Something like:

=A1&TEXT(B1,"0000000")&C1

HTH
Elkar


HTH
Elkar

"TUTU" wrote:

I need to format a series of numbers that are of different lengths to have
leading zeros to be 7 digits. I need these to be able to be merged with 2
other cells to create a field that is a path to photos, to use in a
Publisher merge.
So in cell A1, I have the first part of the path: C:\Documents and
Settings\My Documents\My Pictures\F.
In cell B2 I have the numbers (different numbers like 9, 40, 123, 4567, and
34567) that need to go after the path.
Then I have in cell C2 I have .jpg.
If I use format, custom, 0000000 it works until I merge the cells together.
When they are merged I get C:\Documents and Settings\My Documents\My
Pictures\F40.jpg. I need it to be C:\Documents and Settings\My Documents\My
Pictures\F0000040.jpg

Problem with leading zero's in creating a full path
 

What formula did you use to merge them together?
If you used =A1&TEXT(B2,"0000000")&C2, I can't think why it wouldn't work.
--
David Biddulph

"TUTU" wrote in message
...
I need to format a series of numbers that are of different length's to have
leading zero's to be 7 digits. I need these to be able to be merged with
2
other cells to create a field that is a path to photo's, to use in a
Publisher merge.
So in cell A1, I have the first part of the path: C:\Documents and
Settings\My Documents\My Pictures\F.
In cell B2 I have the numbers (different numbers like 9, 40, 123, 4567,
and
34567) that need to go after the path.
Then I have in cell C2 I have .jpg.
If I use format, custom, 0000000 it works until I merge the cells
together.
When they are merged I get C:\Documents and Settings\My Documents\My
Pictures\F40.jpg. I need it to be C:\Documents and Settings\My
Documents\My
Pictures\F0000040.jpg