Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Leading Zeros
Hi,
I'm recreating a "transID" made up of year,month, date,branch, transaction number in the mainframe. The excel data contains all these fields, but I've had to use formatcellscustom00000 to ensure the transaction number has leading zeros. I'm trying to concatenate these fields, but the transaction number reverts back no leading zeros, so what should be 2006010511100123, looks like 200615111123. I need to exactly replicate the "transID" Any ideas ? Rich |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Leading Zeros
Build it up in pieces formatting each piece the way you want:
dim myStr as string mystr = format(myYear,"0000") & _ format(mymonth,"00") & _ format(myday,"00") & _ and so on.... If that date is one field, you could use: mystr = format(mydate,"yyyymmdd") & _ and so on... If you're using worksheet formulas: =text(a1,"yyyymmdd")&text(b1,"0000")&.... wrote: Hi, I'm recreating a "transID" made up of year,month, date,branch, transaction number in the mainframe. The excel data contains all these fields, but I've had to use formatcellscustom00000 to ensure the transaction number has leading zeros. I'm trying to concatenate these fields, but the transaction number reverts back no leading zeros, so what should be 2006010511100123, looks like 200615111123. I need to exactly replicate the "transID" Any ideas ? Rich -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Leading Zeros
Try something like this:
=TEXT(YEAR(NOW())&TEXT(MONTH(NOW()),"00")&TEXT(DAY (NOW()),"00")&A1&B1,"0000000000000000") HTH JG " wrote: Hi, I'm recreating a "transID" made up of year,month, date,branch, transaction number in the mainframe. The excel data contains all these fields, but I've had to use formatcellscustom00000 to ensure the transaction number has leading zeros. I'm trying to concatenate these fields, but the transaction number reverts back no leading zeros, so what should be 2006010511100123, looks like 200615111123. I need to exactly replicate the "transID" Any ideas ? Rich |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Leading Zeros
I just tried your example, and found that if I enter the information as text (with a leading apostrophe) it works just fine. It also works if you format the cells as text. But then you'll need to find a way to insert the appropriate number of leading zeroes. I'll putz around and see if I can come up with something. -- goto_guy ------------------------------------------------------------------------ goto_guy's Profile: http://www.excelforum.com/member.php...o&userid=30557 View this thread: http://www.excelforum.com/showthread...hreadid=502145 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
retain leading zeros when importing | Excel Discussion (Misc queries) | |||
How to prevent Excel from deleting leading zeros? | Excel Discussion (Misc queries) | |||
Lookup returns #NA when search value (text) has leading zeros. | Excel Worksheet Functions | |||
numbers and text in Excel to read as text keeping the leading zer. | Excel Discussion (Misc queries) |