Formatting the outuput of @Concatenate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I have the following function in a cell:

=CONCATENATE(YEAR(B3)&MONTH(B3)&"-"&UPPER(LEFT(B4,3))&"-
"&DAY(B3))

It generates the following output: 20044-ABC-15
It currently shows 4 digits for the year (2004) and a
single digit for the month (4). I would like it to show
two digits (04) for the month. so that the output would
like like this: 200404-ABC-15

Thanks Ben
 
Hi
try
=CONCATENATE(YEAR(B3)&TEXT(MONTH(B3),"00")&"-"&UPPER(LEFT(B4,3))&"-"&DA
Y(B3))

or skipping the MONTH statement
=CONCATENATE(YEAR(B3)&TEXT(B3,"MM")&"-"&UPPER(LEFT(B4,3))&"-"&DAY(B3))
 
Change MONTH("B3") to TEXT(MONTH(B3),"00").

You don't need CONCATENATE if you are using the concatenation operator "&".
 
You can drop the CONCATENATE:

=YEAR(B3)&TEXT(MONTH(B3),"00")&"-"&UPPER(LEFT(B4,3))&"-
"&DAY(B3)

HTH
Jason
Atlanta, GA
 
Thanks Frank.

Ben
-----Original Message-----
Hi
try
=CONCATENATE(YEAR(B3)&TEXT(MONTH(B3),"00")&"-"&UPPER(LEFT (B4,3))&"-"&DA
Y(B3))

or skipping the MONTH statement
=CONCATENATE(YEAR(B3)&TEXT(B3,"MM")&"-"&UPPER(LEFT(B4,3)) &"-"&DAY(B3))


--
Regards
Frank Kabel
Frankfurt, Germany




.
Click to expand...
 
Thanks Jason.

Ben
-----Original Message-----
You can drop the CONCATENATE:

=YEAR(B3)&TEXT(MONTH(B3),"00")&"-"&UPPER(LEFT(B4,3))&"-
"&DAY(B3)

HTH
Jason
Atlanta, GA

.
Click to expand...
 
Thanks Vasant.

Ben
-----Original Message-----
Change MONTH("B3") to TEXT(MONTH(B3),"00").

You don't need CONCATENATE if you are using the concatenation operator "&".

--

Vasant






.
Click to expand...
 
Back
Top