Combine date with another number in another cell?

**uly609** · 06-19-2012, 03:00 PM

I wanted to know how, or if there is a formula to combine the date with a serial number (I have to inventory a lot of computers) to make an asset control number?
for example:
1/12/2012 607314099

to make 011212099

So I would be taking the date and adding the last 3 digits of the serial number to make the an asset control number. keep in mind that the date and serial number are in two different cells and I want the new number to be in the cell next to the serial number.

Can someone help me do this?

**ChemistB** · 06-19-2012, 03:02 PM

with Date in A1 and Serial Number in A2
=Text(A1,"mmddyy")&RIGHT(A2,3)
Is that what you need?

**Søren Larsen** · 06-19-2012, 03:04 PM

If the date is in A1 and the serial is in B1 then in C1:

=TEXT(DAY(A1),"0#")&TEXT(MONTH(A1),"0#")&YEAR(A1)&" "&RIGHT(B1,3)

**uly609** · 06-19-2012, 03:10 PM

Originally Posted by ChemistB

with Date in A1 and Serial Number in A2
=Text(A1,"mmddyy")&RIGHT(A2,3)
Is that what you need?

This is exactly how I wanted it! now is there a way to automatically apply this formula to the whole column?

**ChemistB** · 06-19-2012, 03:27 PM

Assuming now that Date is in col A, S# in Col B and formula in col C.
=Text(A1,"mmddyy")&RIGHT(B1,3) can be dragged or copied right down the column
Is that how you're set up?

Thank you for the rep.

**uly609** · 06-19-2012, 03:29 PM

yeah thats how I have it setup. I know you can drag it down but is there a way so It automatically generates that number without having to drag it down?

**ChemistB** · 06-19-2012, 03:41 PM

No, the formula needs to be in each cell. There are a number of ways to put the formula into each cell.
With the formula in C1
1. Double clicking on the "Fill Handle" will automatically copy the formula down to the end of your table.
2. In the Name box (where you usually see the Cell address (i.e. C1), Double click and enter C1:C1000. Then hit enter to select that range. Then CNTRL + D to copy the formula down.
3. Copy cell C1, select rest of range and paste.