How do I sort "2018-02-11/20:32:19" records in Excel?

I have series of data that are in format of 2018-02-11/20:32:19. And, I can't work out with formula because of "/" present without any space between date. That "/" some time creates barrier to apply. I don't need options like Filter -"Ctrl+Alt+l" and Sort option under Data tab. I want to know if we can do it like after pasting such data "2018-02-11/20:32:19" in sheet 1 it automatically gets sort or in sheet 2. As we can enter formula in sheet 2 to work upon that.

And also, it has 2 blank rows in between then again such series of different data is present.

Kindly help me out of this issue.

Jamsau

Posted 2018-03-17T22:25:27.967

Reputation: 1

Why not consider and Edit/Replace to change the "/" to a " " space? – Solar Mike – 2018-03-17T22:37:04.873

Or split that column into two based on the / then re-join using a space – ivanivan – 2018-03-17T22:46:56.730

2are those cells formatted as text or as date? – jsotola – 2018-03-17T22:54:23.263

Answers

Select all date cells in both columns.
Copy them into clipboard.
Paste them into Notepad.
In Notepad, open replace dialog and replace / with single space. (Replace All)
Mark all the text in Notepad.
Copy it into clipboard.
Select the top left date in Excel.
Paste the content back into Excel.

Now data become true dates and you can work with them as you need.

Note: by doing search/replace outside original Excel sheet, you preserve potential / character in rest of the cells. Instead of Notepad, for this operation you can use another Excel sheet, other text editor etc.

miroxlav

Posted 2018-03-17T22:25:27.967

Reputation: 9 376

instead of that you can use a separate cell for replacing the character and sort by that column – phuclv – 2018-03-18T01:11:57.110

@LưuVĩnhPhúc – Yes, that is also an alternative. Sometimes it can be more practical, sometimes less, depending on data processing scenario. – miroxlav – 2018-03-18T01:15:51.897

Nothing wrong with the Dates, actually they are in Text format.

You have mentioned that,"I want to know if we can do it like after pasting such data "2018-02-11/20:32:19" in sheet 1 it automatically gets sort or in sheet 2".

I would like to suggest you VBA code, will help you to copy Source data and Paste in the cell of your choice and then sort in Ascending order. After that you can easily paste them in other Sheet.

Private Sub CommandButton1_Click()

Dim xRg As Range
Set xRg = Application.InputBox("Select Cells:", "Select Entire Data Range", Selection.Address, , , , , 8)
xRg.Copy

ActiveSheet.Range("A25").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Sort key1:=Range("A25")

End Sub

Rajesh S

Posted 2018-03-17T22:25:27.967

Reputation: 6 800