0

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.

1

Kindly help me out of this issue.

Improve this question
3
  • Why not consider and Edit/Replace to change the "/" to a " " space?
    – Solar Mike
    Commented Mar 17, 2018 at 22:37
  • Or split that column into two based on the / then re-join using a ` ` space
    – ivanivan
    Commented Mar 17, 2018 at 22:46
  • 2
    are those cells formatted as text or as date?
    – jsotola
    Commented Mar 17, 2018 at 22:54

2 Answers 2

Reset to default
1
  1. Select all date cells in both columns.
  2. Copy them into clipboard.
  3. Paste them into Notepad.
  4. In Notepad, open replace dialog and replace / with single space. (Replace All)
  5. Mark all the text in Notepad.
  6. Copy it into clipboard.
  7. Select the top left date in Excel.
  8. 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.

Improve this answer
2
  • instead of that you can use a separate cell for replacing the character and sort by that column
    – phuclv
    Commented Mar 18, 2018 at 1:11
  • @LưuVĩnhPhúc – Yes, that is also an alternative. Sometimes it can be more practical, sometimes less, depending on data processing scenario.
    – miroxlav
    Commented Mar 18, 2018 at 1:15
0

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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .