Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zero's in Excel | Excel Discussion (Misc queries) | |||
Leading Zero's | Excel Discussion (Misc queries) | |||
Leading zero's missing for SSN numbers... | Excel Discussion (Misc queries) | |||
How to add leading zero's | Excel Worksheet Functions | |||
Leading zero's | Excel Discussion (Misc queries) |