Concatenating Several Rows

**chome4** · 07-05-2010, 05:04 AM

I have two colums containing two cells: 'date and time' and there are several rows showing this.

I want each row to merge the contents so date and time are both next to each other in one cell. I want this replicated all the way down but all the examples I've seen are for one or two rows of data.

Is there a way of merging cells' contents for more than a couple of rows? I've tried recording a macro but it only worked for one row!

**sweep** · 07-05-2010, 05:19 AM

Hi,

Presuming your data starts at A1 and B1

In C1 enter the formula =A1&" "&B1

Press enter

Select C1

Move your cursor over the bottom right corner of the selected cell until it becomes a thin black cross.

Double click.

This should copy the formula all the way down your sheet.

**chome4** · 07-06-2010, 02:49 PM

Originally Posted by sweep

Hi,

Presuming your data starts at A1 and B1

In C1 enter the formula =A1&" "&B1

Press enter

Select C1

Move your cursor over the bottom right corner of the selected cell until it becomes a thin black cross.

Double click.

This should copy the formula all the way down your sheet.

Thank you.

**chome4** · 07-10-2010, 05:27 AM

Works perfectly for character strings but not for numerical data that has to remain unchanged.

For example:

Date and time cells both contain numbers. When the formula is applied, it gives a string of numbers as if a calculation has been done and the result indicated.

CELL 1 CELL 2 FORMULA (CELL 3)
13-Apr-10 15:50 40280 0.991666666666667

CELL 1 CELL 2 CELL 3 SHOULD IDEALLY READ
13-Apr-10 15:50 13-Apr-10 15:50

**teylyn** · 07-10-2010, 06:17 AM

Format the cell to a custom format like

dd-mmm-yy HH:mm

Excel stores dates as whole numbers and time as fractions.

If you have real date values in column A and real time values in column B, you can use a formula in column C

=A1+B1

and format the result with a format that shows both date and time, like the one I mentioned above. Then copy the cell down the rows of data.

**chome4** · 07-12-2010, 05:33 AM

When I select a date cell and choose 'Format Cells', the format 'Custom' is already highlighted with the Type of dd-mmmm-yy.

My date in the first cell, for example, is 12-Apr-10. When I click on it, the fx shows '12/04/2010', which does not correspond with the assumed format above.

The time is in the second cell and for this example is 23:48

=A1+B1 returns the time of 23:48. Not quite correct but 50% of what I want! I've obviously got a problem with the formatting of the date.

**martindwilson** · 07-12-2010, 06:37 AM

here are 2 options first is the way teylyn described

**chome4** · 07-13-2010, 05:13 AM

Originally Posted by martindwilson

here are 2 options first is the way teylyn described

Works perfectly! I didn't think to look at formatting the contents of the resulting cell, C3

Thank you all very much....

**teylyn** · 07-12-2010, 06:38 AM

Post a workbook. If you have a date in column A and a time in column B, then in column C you have A1+B1, then you can simply format it to show as <date> <time> , where <date> is your preferred date format and <time> is your preferred time format.

When I select a date cell and choose 'Format Cells', the format 'Custom' is already highlighted with the Type of dd-mmmm-yy.

You can change a custom format to any format you want to apply.

yy is year in 2 digits
yyyy is year in 4 digits
mm is month in numerals
mmm is month as 3 letter text
mmmm is month as full month name
dd is day in numerals
ddd is day as three letter weekday
dddd is day as full weekday name
ddddd is first letter of the weekday
hh is hour in am/pm format
HH is hour in 24 hour format

so, just pick and choose your preferred date/time combination in the custom format. For example

dd-mmm-yyyy HH:mm will give you 19-May-2010 20:23
d/m/yy hh:mm will give you 19/5/10 8:23 pm
dddd, mm.dd.yyyy hh:mm will give you Sunday, 05.19.2010 8:23 pm (note, I can't be bothered to verify what weekday that day really was this year)

That's the idea of "custom" format. You can "customise" it to suit your needs.