Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

dates column format single

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dates in column format to a single cell

Dates in column format to a single cell

ResolvedVersion 2010

Aysha has attended:
Access Introduction course
Excel Pivot Tables course

Dates in column format to a single cell

Hello,

I have a list of dates entered from A1:A10, I want to copy all of these dates and enter them into a single cell C1

Currently:
10/10/2010
11/11/2011
12/12/2012

Would like it displayed as:
10/10/2010, 11/11/2011, 12/12/2012

I would like to be able to do this without having to select each date individually, copy and then go to the new cell and paste it in.

RE: Dates in column format to a single cell

Hi Aysha,

Thank you for the forum question.

You cannot paste it to a single cell, but you can write a formula.

In C1 type:

=A1&", "&A2&", "&A3&", "&A4&", "&

and continue the same way with the rest of the cells.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Dates in column format to a single cell

Hello,

I found an easier way to do this using VBA code which reverses the text to columns function and also allows to select a large range if needed:

Public Function ReverseTextToColumns(Rg As Range, Optional D As String = " ") As String
'updateby Extendoffice 20160406
Dim xArr
xArr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(Rg.Value))
ReverseTextToColumns = Join(xArr, D)
End Function

I then copy and paste special transpose my column data e.g. (A1:A10) so that it is now in rows (B1:K1) and then in L1 I type =reversetexttocolumns(B1:K1,",")

RE: Dates in column format to a single cell

Hi Aysha,

Well done. It is a good solution.

I am using vba to do all my repeating tasks.

If you in the future want a VBA solution please raise a vba question. If it is not raised as a vba question we will return a worksheet solution.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 4 Nov 2016: Automatically marked as resolved.

Excel tip:

Removing border lines on the keyboard

Highlight your cell(s) that have boreders on them and press CTRL + SHIFT + _, this will then remove the border lines.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.