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

You need to do two things

1) You need to combine your strings with "&"
2) You can't store the number 0000040 into a variable declared as an
integer, or variant, or no type (ie Dim X) which is a variant. An integer
variable will remove the leading zeroes. The variable should be delcared as
a string.

"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

Maybe...

=$a$1&text(b2,"0000000")&c2



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

--

Dave Peterson

Thank you Dave, that worked, and was so easy.

"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