Extract date from text string conditional on words in string

[TNLondon]

Hi all

new to this forum and desperate for help, please!

I am trying to extract specific dates from an audit trail for a booking, which is all in one cell. The audit trail looks like this, it is in cell A2:

Created at 02/01/2013 08:16 by Smith, John Updated at 02/01/2013 08:16 by Smith, John Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 18/01/2013 11:15 by Doe, Jane Changed Hours Booked from "427.50" to "378.75" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Start Date from "2013-01-16" to "2013-02-04" Updated at 07/02/2013 11:50 by Doe, Jane Changed Hours Booked from "378.75" to "217.50" Changed End Date from "2013-06-28" to "2013-03-29" Changed Region of Project [Custom field] from "Europe" to "Europe - South" Changed % Booked from "50.00" to "100.00" Changed Start Date from "2013-02-04" to "2013-02-18" Updated at 06/03/2013 14:11 by Doe, Jane Changed Hours Booked from "217.50" to "112.50" Changed End Date from "2013-03-29" to "2013-03-08" Updated at 08/03/2013 05:44 by Doe, Jane Changed Hours Booked from "112.50" to "82.50" Changed End Date from "2013-03-08" to "2013-03-04"

The first step of the trail starts with "Created", all others steps with "Updated".

I want to extract the date highlighted in red, which is the date when a booking type was first updated to "Approved - Hard Booking" or "Approved - Soft Booking" (this can happen multiple times, but I only want the first date of this audit trail step). The length of the string can vary, also the number of characters between the date I want to extract and the "Approved - Hard Booking".

I think this must be possible with left/right/mid formulas which I've done before, but this one exceeds my Excel skills by far. Can anyone help me, please?

Many thanks in advance!
Tom

 

[Rick Rothstein]

Assuming the text is always exactly this (note the quote mark in front of Approve)...

Updated at

and this

"Approve -

where each of those have a trailing space after them, then this formula should work...

=LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH("""Approved - ",A1)-1),"Updated at ",CHAR(1)),CHAR(1),REPT(" ",999)),999)),16)

It returns a text value, not a real date/time. If you need a real date/time, use this instead...

=1*LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH("""Approved - ",A1)-1),"Updated at ",CHAR(1)),CHAR(1),REPT(" ",999)),999)),16)

 

[TNLondon]

WOW - thank you so much, this is amazing!

I used the 2nd formula you provided for 10,000 records and it works a treat on every single one of them!

One last question if I may: How can I amend the formula to also extract the name, i.e. Doe, Jane from the same string in bold into a new cell?

Many thanks again
Tom

 

[Rick Rothstein]

One last question if I may: How can I amend the formula to also extract the name, i.e. Doe, Jane from the same string in bold into a new cell?

That may be tricky depending on how names can be structured. Names might contain more than one "word"; for example, people I used to work with before retiring... Mary Ann Jones and Joe Della Rossa... "Mary Ann" is her actual first name (both "words) and "Della Rossa" was his full last name (again, both words). And just think if they were married to each other... Mary Ann Della Rossa (first and last name only, no middle names). So, unless the word "Changed" always followed the name, there is no way to be sure the formula will have pulled in the entire person's name. If you want me to simply assume the first name will always be a single "word", I could give you a formula for that, but it will misidentify Mary Ann as Mary (which, by the way, she hated when people made that assumption about her name). Let me know whether "Changed" always follows the name or if you are willing to pull a single "word" first name no matter what.

 

[TNLondon]

Hi Rick

I had a look at the data - I think "Changed" always follows the person's name. The names are always structured as "Last Name(s), First Name(s)". So in your example the string would show "Doe, Jane", "Jones, Mary Ann" or "Della Rossa, Joe".

Not sure if it helps, but the number of names that would come up - while we have 10,000s of resource bookings, we only have ca. 20 resource managers, and their names are known. Would a lookup from a table or a range work?

Best regards
Tom

 

[TheCman81]

i Hope you don't mind Rick but I took your formula and adapted it.

Excel 2010

	A
Created at 02/01/2013 08:16 by Smith, John Updated at 02/01/2013 08:16 by Smith, John Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 18/01/2013 11:15 by Doe, Jane Ann Changed Hours Booked from "427.50" to "378.75" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Start Date from "2013-01-16" to "2013-02-04" Updated at 07/02/2013 11:50 by Doe, Jane Changed Hours Booked from "378.75" to "217.50" Changed End Date from "2013-06-28" to "2013-03-29" Changed Region of Project [Custom field] from "Europe" to "Europe - South" Changed % Booked from "50.00" to "100.00" Changed Start Date from "2013-02-04" to "2013-02-18" Updated at 06/03/2013 14:11 by Doe, Jane Changed Hours Booked from "217.50" to "112.50" Changed End Date from "2013-03-29" to "2013-03-08" Updated at 08/03/2013 05:44 by Doe, Jane Changed Hours Booked from "112.50" to "82.50" Changed End Date from "2013-03-08" to "2013-03-04"
Doe, Jane Ann

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND(LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("Approved - ",A1)-1),"Updated at ",REPT(" ",999)),999)),16),A1)+19),"")," ",REPT(" ",100)),250))," Changed","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[TNLondon]

Hi Cman81
thank you very much for the suggestion. The formula returns names in all cases, but I found two problems with it.

1) In ca. 5% of cases the formula returns "updated" after the first name, and whenever this is the case it also returns the wrong name:

Created at 19/02/2013 08:44 by Janakiraman, Swaminathan Updated at 19/02/2013 08:44 by Gore, Deodatta Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Updated at 24/02/2013 12:40 by Picardi, Stephanie using the API Changed Client from "Client A" to "Client B" Changed Project from "Project A" to "Project B" Updated at 25/02/2013 12:54 by Picardi, Stephanie using the API Changed Project from "Project B" to "Project C"

Using your formula on this one returns "Janakiraman, Swaminathan Updated ". So "Updated" is shown after the last name in the result, but it's also the wrong name. It should have displayed "Gore, Deodatta". It's strange behaviour because in the 95% of cases where the formula returns only the name, then it's the correct name. No idea why

2) In some cases we allow users to log into the system as other users, this is called a proxy. A string would then look like this:

Created at 27/02/2013 05:56 by Anstey, Kathleen Updated at 27/02/2013 05:56 by Anstey, Kathleen Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Region A" Updated at 06/03/2013 04:30 by Anstey, Kathleen (Proxy by Pereira, Jyothi) Changed Booking type from "Approved - Soft Booking" to "Approved - Hard Booking" Changed Client from "Client A" to "Client B" Changed Project from "Project A" to "Project B"

Your formula returns "Anstey, Kathleen (Proxy" which is the correct name in all cases - but I would like it to display either just "Anstey, Kathleen" (preferred) or "Anstey, Kathleen (Proxy by Pereira, Jyothi)", whichever is possible.

Many thanks for your help.
Tom

 

[TheCman81]

Created at 19/02/2013 08:44 by Janakiraman, Swaminathan Updated at 19/02/2013 08:44 by Gore, Deodatta Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Updated at 24/02/2013 12:40 by Picardi, Stephanie using the API Changed Client from "Client A" to "Client B" Changed Project from "Project A" to "Project B" Updated at 25/02/2013 12:54 by Picardi, Stephanie using the API Changed Project from "Project B" to "Project C"

This is because your approved time is the same as your created time and this formula is looking at the first instance of the date and time.

I'll get back to you in a bit on a solution

 

[TheCman81]

This will sort out your issue if you have duplicate date and times within your text and should return the correct name.

I've only built this to return 2 blocks of name ie "Joe Doe", "Gore, Deodatta" etc as it will be very difficult to determain something like this "Anstey, Kathleen" (preferred) or "Anstey, Kathleen (Proxy by Pereira, Jyothi)"

Let me know how you get on

Excel 2010
	A	B
1	Created at 02/01/2013 08:16 by Smith, John Updated at 02/01/2013 08:16 by Smith, John Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 18/01/2013 11:15 by Doe, Jane Changed Hours Booked from "427.50" to "378.75" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Start Date from "2013-01-16" to "2013-02-04" Updated at 07/02/2013 11:50 by Doe, Jane Changed Hours Booked from "378.75" to "217.50" Changed End Date from "2013-06-28" to "2013-03-29" Changed Region of Project [Custom field] from "Europe" to "Europe - South" Changed % Booked from "50.00" to "100.00" Changed Start Date from "2013-02-04" to "2013-02-18" Updated at 06/03/2013 14:11 by Doe, Jane Changed Hours Booked from "217.50" to "112.50" Changed End Date from "2013-03-29" to "2013-03-08" Updated at 08/03/2013 05:44 by Doe, Jane Changed Hours Booked from "112.50" to "82.50" Changed End Date from "2013-03-08" to "2013-03-04"	Doe, Jane
2	Created at 19/02/2013 08:44 by Janakiraman, Swaminathan Updated at 19/02/2013 08:44 by Gore, Deodatta Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Updated at 24/02/2013 12:40 by Picardi, Stephanie using the API Changed Client from "Client A" to "Client B" Changed Project from "Project A" to "Project B" Updated at 25/02/2013 12:54 by Picardi, Stephanie using the API Changed Project from "Project B" to "Project C"	Gore, Deodatta
3	Created at 27/02/2013 05:56 by Anstey, Kathleen Updated at 27/02/2013 05:56 by Anstey, Kathleen Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Region A" Updated at 06/03/2013 04:30 by Anstey, Kathleen (Proxy by Pereira, Jyothi) Changed Booking type from "Approved - Soft Booking" to "Approved - Hard Booking" Changed Client from "Client A" to "Client B" Changed Project from "Project A" to "Project B"	Anstey, Kathleen
Sheet2

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND(LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("""Approved - ",A1)-1),"Updated at ",REPT(" ",999)),999)),16),A1,FIND(LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("""Approved - ",A1)-1),"Updated at ",REPT(" ",999)),999)),16),A1)+1)+19),"")," ",REPT(" ",100)),207)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND(LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("""Approved - ",A1)-1),"Updated at ",REPT(" ",999)),999)),16),A1)+19),"")," ",REPT(" ",100)),207)))

 

[TNLondon]

Sorry for the delay in response, I just checked ca. 20,000 records and it took a while

All names are coming out perfectly now, so my first issue is sorted.

The second issue: I found a few records where the wrong name is still being returned (<1%): A couple of examples below, but if it cannot be solved then I think I can live with this level of inaccuracy => you guys have made me very happy already, thank you so much!!!

Created at 07/11/2012 05:25 by Baroukh, Benjamin Updated at 07/11/2012 05:25 by Baroukh, Benjamin Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 07/11/2012 05:25 by Price, Alan Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Updated at 10/12/2012 07:35 by Gray, Niki Updated at 10/12/2012 07:36 by Gray, Niki Updated at 13/12/2012 07:39 by Tarane, Gayatri

This should return "Price, Alan" but it returns "Baroukh, Benjamin" instead.

Created at 21/01/2013 02:37 by Sacranie, Nazir Updated at 21/01/2013 02:37 by Sacranie, Nazir Changed Product [Custom field] from "" to "Product A" Changed Region of Project [Custom field] from "" to "MEA" Updated at 21/01/2013 02:37 by Anstey, Kathleen Changed Notify requester from "No" to "Yes" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Booking Location [Custom field] from "" to "On Site" Updated at 22/01/2013 02:21 by Anstey, Kathleen Changed Hours Booked from "40.00" to "72.00" Changed Start Date from "2013-03-21" to "2013-03-16" Updated at 14/02/2013 01:36 by Anstey, Kathleen Changed Booking type from "Approved - Hard Booking" to "Request - Soft Booking" Updated at 14/02/2013 02:03 by Anstey, Kathleen Changed End Date from "2013-03-27" to "2013-05-01" Changed Start Date from "2013-03-16" to "2013-04-20" Updated at 14/02/2013 02:13 by Anstey, Kathleen Changed Booking type from "Request - Soft Booking" to "Approved - Hard Booking" Updated at 18/02/2013 03:05 by Anstey, Kathleen Changed Booking type from "Approved - Hard Booking" to "Request - Hard Booking" Updated at 18/02/2013 03:05 by Anstey, Kathleen Changed End Date from "2013-05-01" to "2013-04-24" Changed Start Date from "2013-04-20" to "2013-04-13" Updated at 18/02/2013 03:22 by Anstey, Kathleen Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking"

This should return "Anstey, Kathleen" but it returns "Sacranie, Nazir instead.