Originally posted by BoredBloke
View Post
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Excel jiggery-pokery
Collapse
X
-
Rule Number 1 - Assuming that you have a valid contract in place always try to get your poo onto your timesheet, provided that the timesheet is valid for your current contract and covers the period of time that you are billing for.
I preferred version 1! -
Originally posted by BoredBloke View PostJuts read the bit above, so that won't work!!"Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.Comment
-
-
Originally posted by mudskipper View PostIf this is autogenerated, can you assume newest is always on top?"Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.Comment
-
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.…Maybe we ain’t that young anymoreComment
-
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?Rule Number 1 - Assuming that you have a valid contract in place always try to get your poo onto your timesheet, provided that the timesheet is valid for your current contract and covers the period of time that you are billing for.
I preferred version 1!Comment
-
Originally posted by BoredBloke View PostIs 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?…Maybe we ain’t that young anymoreComment
-
Originally posted by BoredBloke View PostIs 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?Originally posted by WTFH View PostI believe it's all one cell, otherwise there is no requirement to split out the data.
I need everything from and including the first time stamp up to but excluding the second time stamp."Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.Comment
-
Originally posted by DaveB View PostI need everything from and including the first time stamp up to but excluding the second time stamp.
Originally posted by WTFH View Post=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.
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.…Maybe we ain’t that young anymoreComment
-
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'tLast edited by BoredBloke; 17 October 2018, 09:41.Rule Number 1 - Assuming that you have a valid contract in place always try to get your poo onto your timesheet, provided that the timesheet is valid for your current contract and covers the period of time that you are billing for.
I preferred version 1!Comment
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Comment