Reply to: Excel jiggery-pokery

[DaveB]

=LEFT(A1,IFERROR(((FIND("-20",A1,10))-6),LEN(A1)))

Gets round the bug in version 1.
The IFERROR function returns the value if it gets a match, if it doesn't get a match (i.e. goes into error) then it returns the length of the cell

Bingo!

I owe you a pint

[WTFH]

=LEFT(A1,IFERROR(((FIND("-20",A1,10))-6),LEN(A1)))

Gets round the bug in version 1.
The IFERROR function returns the value if it gets a match, if it doesn't get a match (i.e. goes into error) then it returns the length of the cell

[DaveB]

Will the last test of the first line always be "(Customer Updates)" ? (or maybe even just " Updates)"

This is dirty, and strips out the date/time/etc on the first line... but works:
=MID(A1,FIND("Updates)",A1,1)+9,(FIND("-20",A1,10)-FIND("Updates)",A1,1))-15)

This will work for any year in the 21st century as long as "-20" and "Updates)" don't appear in the free text

or, if you want to include the date & name:
=LEFT(A1,(FIND("-20",A1,10))-6)

This will work for any year in the 21st century as long as "-20" doesn't appear in the free text of the most recent comment.

Might try to refine it some more.

Do need the date and timestamp.

=LEFT(A1,(FIND("-20",A1,10))-6)

This works, but breaks if there is only one entry, which I hadnt thought about as there was only actually one row where that was the case.

[BoredBloke]

Ok if all the text is wrapped in one cell (a1) then try this

Sub StripData()
Dim ws As Worksheet

Dim sVal As String
Dim sComment As String

Dim lOutRow As Long
Dim vData As Variant
Dim lCounter As Long


Set ws = ActiveSheet
sVal = ws.Cells(2, 1)
lOutRow = 1
vData = Split(sVal, Chr(10))
For lCounter = 0 To UBound(vData)
If IsDate(Left(vData(lCounter), 10)) Then
'split out values
sComment = ""
lOutRow = lOutRow + 1
ws.Cells(lOutRow, 2) = Left(vData(lCounter), 10)
ws.Cells(lOutRow, 3) = Trim(Mid(vData(lCounter), 12, 8))
ws.Cells(lOutRow, 4) = Trim(Right(vData(lCounter), Len(vData(lCounter)) - 23))
Else
sComment = sComment & vData(lCounter)
ws.Cells(lOutRow, 5) = sComment
End If
Next lCounter

Set ws = Nothing


End Sub

Sorry meant to say this code assumes the data is all in cell A2 (sVal = ws.Cells(2, 1)) row 2, column 1 = A2. It then writes the data out into columns 2 to 5 (b to e) and adds lines as it needs them - would add a screen shot of the output but can't

[WTFH]

I need everything from and including the first time stamp up to but excluding the second time stamp.

=LEFT(A1,(FIND("-20",A1,10))-6)

This will work for any year in the 21st century as long as "-20" doesn't appear in the free text of the most recent comment.

If you need it as VBA it's going to be more complex!

Just to explain the logic:
Using "LEFT" as you want the first record in the field. Would use "MID" for others, or "RIGHT" for the last record.
-20 appears as part of the date 12-10-2018
We want to find the second occurrence of the date, so we're looking for "-20" that occurs more than 10 characters in to the field. <note: just realised as typing this that if there is only one record in the field, this will fail, need to rework>
Once we find the second "-20" we then need to count back 6 characters as the preceding 6 characters are the start of that date field.

[DaveB]

Is all the text wrapped in one cell of is it cell

A1 =
12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.
A2 =
05-10-2018 08:24:24 - Jane Blogges (Customer Updates)
Supplier has produced a more detail proposal for the solution, including a move to an integrated service built on the exiting API's

Costs yet to be agreed.

and so on?

I believe it's all one cell, otherwise there is no requirement to split out the data.

What WTFH said.

I need everything from and including the first time stamp up to but excluding the second time stamp.

[WTFH]

Is all the text wrapped in one cell of is it cell

A1 =
12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.
A2 =
05-10-2018 08:24:24 - Jane Blogges (Customer Updates)
Supplier has produced a more detail proposal for the solution, including a move to an integrated service built on the exiting API's

Costs yet to be agreed.

and so on?

I believe it's all one cell, otherwise there is no requirement to split out the data.

[BoredBloke]

Is all the text wrapped in one cell of is it cell

A1 =
12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.
A2 =
05-10-2018 08:24:24 - Jane Blogges (Customer Updates)
Supplier has produced a more detail proposal for the solution, including a move to an integrated service built on the exiting API's

Costs yet to be agreed.

and so on?

[WTFH]

Will the last test of the first line always be "(Customer Updates)" ? (or maybe even just " Updates)"

This is dirty, and strips out the date/time/etc on the first line... but works:
=MID(A1,FIND("Updates)",A1,1)+9,(FIND("-20",A1,10)-FIND("Updates)",A1,1))-15)

This will work for any year in the 21st century as long as "-20" and "Updates)" don't appear in the free text

or, if you want to include the date & name:
=LEFT(A1,(FIND("-20",A1,10))-6)

This will work for any year in the 21st century as long as "-20" doesn't appear in the free text of the most recent comment.

Might try to refine it some more.

[DaveB]

If this is autogenerated, can you assume newest is always on top?

Yes. It is always the top entry I need, but length of content is arbitrary.

[mudskipper]

If this is autogenerated, can you assume newest is always on top?

[DaveB]

Juts read the bit above, so that won't work!!

You're right, it doesn't

[BoredBloke]

Sub StripData()
Dim ws As Worksheet
Dim lLR As Long
Dim lRowCounter As Long
Dim sVal As String
Dim sDate As String
Dim sTime As String
Dim sName As String
Dim sComment As String
Dim sTest1 As String
Dim sTest2 As String
Dim lOutRow As Long


lOutRow = 2

Set ws = ThisWorkbook.Sheets(1)
lLR = ws.UsedRange.Rows.Count
For lRowCounter = 2 To lLR
sVal = ws.Cells(lRowCounter, 1)
sTest1 = Replace(sVal, "-", "")
sTest2 = Replace(sVal, ":", "")
If Len(sVal) - Len(sTest1) >= 3 And Len(sVal) - Len(sTest2) >= 2 Then
sComment = ""
lOutRow = lOutRow + 1
sDate = Left(sVal, 10)
sTime = Mid(sVal, 11, 9)
sName = Right(sVal, Len(sVal) - 19)
ws.Cells(lOutRow, 3) = sDate
ws.Cells(lOutRow, 4) = sTime
ws.Cells(lOutRow, 5) = sName
Else
If sVal <> "" Then
sComment = sComment & Chr(10) & sVal
ws.Cells(lOutRow, 6) = sComment
End If
End If
Next lRowCounter

Set ws = Nothing


End Sub
raw data goes in column A on sheet 1 and output gets dumped to columns CDE&F on the same sheet

Juts read the bit above, so that won't work!!

[BoredBloke]

Sub StripData()
Dim ws As Worksheet
Dim lLR As Long
Dim lRowCounter As Long
Dim sVal As String
Dim sDate As String
Dim sTime As String
Dim sName As String
Dim sComment As String
Dim sTest1 As String
Dim sTest2 As String
Dim lOutRow As Long


lOutRow = 2

Set ws = ThisWorkbook.Sheets(1)
lLR = ws.UsedRange.Rows.Count
For lRowCounter = 2 To lLR
sVal = ws.Cells(lRowCounter, 1)
sTest1 = Replace(sVal, "-", "")
sTest2 = Replace(sVal, ":", "")
If Len(sVal) - Len(sTest1) >= 3 And Len(sVal) - Len(sTest2) >= 2 Then
sComment = ""
lOutRow = lOutRow + 1
sDate = Left(sVal, 10)
sTime = Mid(sVal, 11, 9)
sName = Right(sVal, Len(sVal) - 19)
ws.Cells(lOutRow, 3) = sDate
ws.Cells(lOutRow, 4) = sTime
ws.Cells(lOutRow, 5) = sName
Else
If sVal <> "" Then
sComment = sComment & Chr(10) & sVal
ws.Cells(lOutRow, 6) = sComment
End If
End If
Next lRowCounter

Set ws = Nothing


End Sub
raw data goes in column A on sheet 1 and output gets dumped to columns CDE&F on the same sheet

[DaveB]

OK. Looking at the rows above, what is your desired output?

12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.

Essentially everything from the first time stamp, which is always the first entry in the field, to the next time stamp down.

The lines above are all from the same cell in the spreadsheet, they are not individual rows.