software

Hi,

I'm recreating a quot;transIDquot; 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 formatgt;cellsgt;customgt;00000 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 quot;transIDquot;

Any ideas ?

RichBuild it up in pieces formatting each piece the way you want:

dim myStr as string
mystr = format(myYear,quot;0000quot;) amp; _
format(mymonth,quot;00quot;) amp; _
format(myday,quot;00quot;) amp; _
and so on....

If that date is one field, you could use:
mystr = format(mydate,quot;yyyymmddquot;) amp; _
and so on...

If you're using worksheet formulas:

=text(a1,quot;yyyymmddquot;)amp;text(b1,quot;0000quot;)amp;....wrote:
gt;
gt; Hi,
gt;
gt; I'm recreating a quot;transIDquot; made up of year,month, date,branch,
gt; transaction number in the mainframe. The excel data contains all these
gt; fields, but I've had to use formatgt;cellsgt;customgt;00000 to ensure the
gt; transaction number has leading zeros.
gt;
gt; I'm trying to concatenate these fields, but the transaction number
gt; reverts back no leading zeros, so what should be 2006010511100123,
gt; looks like 200615111123.
gt;
gt; I need to exactly replicate the quot;transIDquot;
gt;
gt; Any ideas ?
gt;
gt; Rich

--

Dave Peterson

Try something like this:

=TEXT(YEAR(NOW())amp;TEXT(MONTH(NOW()),quot;00quot;)amp;TEXT(DAY (NOW()),quot;00quot;)amp;A1amp;B1,quot;0000000000000000quot;)

HTH
JG

quot; wrote:

gt; Hi,
gt;
gt; I'm recreating a quot;transIDquot; made up of year,month, date,branch,
gt; transaction number in the mainframe. The excel data contains all these
gt; fields, but I've had to use formatgt;cellsgt;customgt;00000 to ensure the
gt; transaction number has leading zeros.
gt;
gt; I'm trying to concatenate these fields, but the transaction number
gt; reverts back no leading zeros, so what should be 2006010511100123,
gt; looks like 200615111123.
gt;
gt; I need to exactly replicate the quot;transIDquot;
gt;
gt; Any ideas ?
gt;
gt; Rich
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30557
View this thread: www.excelforum.com/showthread...hreadid=502145