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