given a string of data in 1 row, how can i create a new row every time a date comes up?

[purelutz]

can this be done in excel? i don't want to manually start a new row every time there is a date. See attachment and scroll down until you see a lot of data jumbled together for an example. I've started what i want the data to look like at the top of the file. Thanks for the help.

Example of what the data looks like:
16-oct 4081931 -4,517.60 check 13454543 16-oct 4081931 -4,517.60 check 13454543 16-oct 4081931 -4,517.60 check 13454543 16-oct 4081931 -4,517.60 check 13454543 16-oct 4081931 -4,517.60 check 13454543

Example of what i want:
16-oct 4081931 -4,517.60 check 13454543
16-oct 4081931 -4,517.60 check 13454543
16-oct 4081931 -4,517.60 check 13454543
16-oct 4081931 -4,517.60 check 13454543

[mehmetcik]

In your example all the dates are in Oct.

Is it normal to have all the dates in one month?

This Macro will split the active cell into seperate rows as requested. It will work for all months.

Sub Test()

A = ActiveCell.Value
A = Application.Trim(A)

R = ActiveCell.Row

On Error Resume Next

For MCount = 1 To 12

Pos = 1

SText = "-" & Format(DateValue("01/" & MCount & "/2017"), "MMM")

Loop1:

Pos = InStr(Pos, A, SText)

If Pos = 0 Then GoTo Skip

Pos2 = InStrRev(A, " ", Pos)

If Pos2 = 0 Then Pos = Pos + 1: GoTo Loop1
Cells(R, 1).Value = Left(A, Pos2 - 1)
Cells(R + 1, 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

A = Right(A, Len(A) - Pos2)

Pos = 1: R = R + 1: GoTo Loop1

Skip:
Cells(R, 1).Value = A

Next MCount

End Sub

[purelutz]

All october, yes. the day can change though.

[mehmetcik]

Sub Test()

Dim fNameAndPath As Variant

fNameAndPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub

Workbooks.OpenText Filename:=fNameAndPath, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
        TrailingMinusNumbers:=True

LR = Cells(Rows.Count, 1).End(xlUp).Row

Range("B1:B" & LR).FormulaR1C1 = _
        "=IF(CODE(LEFT(RC[-1],1))=9,RIGHT(RC[-1],LEN(RC[-1])-1),RC[-1])"
Range("A1:A" & LR).Value = Range("B1:B" & LR).Value

Range("B1:B" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(LEFT(RC[-1],1)*1),RC[-1],RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])+3))"
Range("A1:A" & LR).Value = Range("B1:B" & LR).Value

Range("B1:B" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(LEFT(RC[-1],1)*1),RC[-1],RIGHT(RC[-1],LEN(RC[-1])-1))"
Range("A1:A" & LR).Value = Range("B1:B" & LR).Value

For MCount = 10 To 12

LR = Cells(Rows.Count, 1).End(xlUp).Row

SText = "-" & Format(DateValue("01/" & MCount & "/2017"), "MMM")

Columns("A:A").Replace What:=SText, Replacement:=SText & " ", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Range("B1:B" & LR).FormulaR1C1 = _
        "=(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],""" & SText & ""","""")))/4"
        
    Range("B1:B" & LR).Value = Range("B1:B" & LR).Value
    
        Columns("B:B").Replace What:="1", Replacement:=0, Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Loop2:
P = Application.Max(Columns(2).Value)

If P = 0 Then GoTo Skip2

Max = 30 + P

Set Z = Columns(2).Find(P, LookIn:=xlValues, Lookat:=xlWhole)
        
Z.Clear

A = Application.Trim(Z.Offset(0, -1).Value)

R = Z.Row

On Error Resume Next

SText = Left(A, InStr(A, " ") - 1)

Loop1:

Pos = InStr(7, A, SText)

If Pos = 0 Then GoTo Skip

Cells(R, 1).Value = Left(A, Pos - 1)
Range(Cells(R + 1, 1), Cells(R + 1, 2)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

A = Right(A, Len(A) - Pos + 1)

R = R + 1: GoTo Loop1

Skip:

SText = Left(CStr(Format(DateValue(Replace(SText, "-", "/") & "/" & Year(Date)) + 1, "dd-mmm-yyyy")), 6)
Max = Max - 1
If Max > 0 Then GoTo Loop1

Cells(R, 1).Value = A

GoTo Loop2

Skip2:
Next MCount


End Sub